MySQL INSERT INTO SELECT SET

Question

I'm trying to copy a table to the columns of a different table but the date fields are not valid(Date are like '00000000') for some of the columns so I'm trying to check if the date is valid and I'm trying to set it NULL if so. Example,

INSERT INTO table1 (ID, FirstName, LastName, @BD) SELECT * FROM table2 SET Birthday = IF(@BD = '00000000', NULL, CAST(@BD as DATE))

This format works for LOAD while loading data from a CSV file but gives and error saying the syntax is wrong. Also, table2 columns are in this order ID, FirstName, LastName, and Birthday. Thanks in advance!

What is the syntax error ? Please edit your question to include the error details! — Madhur Bhaiya
– Madhur Bhaiya, Commented Sep 19, 2018 at 13:39
SET more more commonly used with UPDATE.. MySQL supports INSERT INTO ... SET ... but i advice you never to use it because it's not a valid ANSI SQL insert query. — Raymond Nijland
– Raymond Nijland, Commented Sep 19, 2018 at 14:00

Joakim Danielson · Accepted Answer · 2018-09-19 13:44:20Z

Have you tried converting during select instead

INSERT INTO table1 (ID, FirstName, LastName, Birthday) SELECT ID, FirstName, LastName, CASE BirthDay WHEN '00000000' THEN NULL ELSE CAST(BirthDay as DATE) END FROM table2

Collectives™ on Stack Overflow

MySQL INSERT INTO SELECT SET

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related