so it's work for me..
INSERT INTO `requests`( `id`, `ctg`, `msg`, `nick`, `dated`, `filled` )
SELECT `fid`,
`ctg`,
`msg`,
`id`,
`date`,
CASE `status`
WHEN 'empty' THEN 'N'
WHEN 'filled' THEN 'Y'
END
FROM `request`
To avoid putting values two times in your INSERT statement use VALUES(<column_name>) function.
INSERT ... ON DUPLICATE KEY UPDATE Syntax
You can use the VALUES(col_name) function in the UPDATE clause to
refer to column values from the INSERT portion of the INSERT ... ON
DUPLICATE KEY UPDATE statement. In other words, VALUES(col_name) in
the ON DUPLICATE KEY UPDATE clause refers to the value of col_name
that would be inserted, had no duplicate-key conflict occurred. This
function is especially useful in multiple-row inserts. The VALUES()
function is meaningful only in INSERT ... UPDATE statements and
returns NULL otherwise.
Unfortunately it's not very clear in your question what is your desired outcome. But assuming that you want update only columns that have NULL or empty strings you can do
INSERT INTO some_table (f_name, l_name, work)
VALUES ('Dotan', '', 'php')
ON DUPLICATE KEY UPDATE
f_name = COALESCE(NULLIF(f_name, ''), VALUES(f_name)),
l_name = COALESCE(NULLIF(l_name, ''), VALUES(l_name)),
work = COALESCE(NULLIF(work, ''), VALUES(work));
Here is SQLFiddle demo
If it's not exactly what you want, please consider to post your desired outcome and explain what values under what conditions you want to update
Best Answer
When you write your INSERT query:
In this way the DBMS thinks you try to put in your date field a string named "NOW()" instead of function to retrieve the current timestamp
You must remove the ' ' around NOW() function. In this way: