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, Sep 19 '18 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, Sep 19 '18 at 14:00

score 3 · Answer 1 · answered Sep 19 '18 at 13:44

3

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

answered Sep 19 '18 at 13:44

Joakim Danielson

43,251
5
22
52

MySQL INSERT INTO SELECT SET

1 Answers1