INSERT INTO travel_cards (card_number, job_during_journey, colonist_id, journey_id)

SELECT
IF(birth_date>='1980-01-01',
CONCAT(
YEAR(birth_date),
DATE_FORMAT(birth_date, '%d'),
LEFT(ucn, 4)
),
CONCAT(YEAR(birth_date),
DATE_FORMAT(birth_date, '%m'),
right(ucn, 4)
)
) AS card_number,
CASE
WHEN id MOD 2 = 0 THEN
	'Pilot'
WHEN id mod 3 = 0 THEN
	'Cook'
ELSE
	'Engineer'
END AS job_during_journey,
id AS colonist_id,
LEFT(ucn, 1) AS journey_id
 FROM colonists
WHERE id BETWEEN 96 AND 100