concatenate curdate() with time based on another table

Question

I'm trying to store the concatenation of current date, and time from another table, based on a comparison of a column in that table, I've tried this code, but it gives me mysql syntax error.What is wrong?

My Code:

UPDATE daily_table
SET
task_datetime =  CASE
    when master_table.check_day = 'Today' then concat(curdate(),'   ',master_table.task_time)          
 when master_table.check_day = 'Tomorrow' then concat(   date_add(curdate(),interval 1 day) ,' ', master_table.task_time)
 CASE END

I'm using mysql and phpmyadmin.

@Charvee Shah, its working, im getting this, Unknown column 'master_table.check_day' in 'field list' — user1930115, Mar 09 '15 at 06:26
@ Stanislovas Kalašnikovas master_id(int),task_name(varchar 255), task_time(time),check_day(varchar 255) , The check_day column stores a string either 'Today' or 'Tomorrow' — user1930115, Mar 09 '15 at 06:56
You didnt do join of master_table that's why it gives error. — Charvee Shah, Mar 09 '15 at 07:18

score 0 · Answer 1 · edited May 23 '17 at 12:06

You should JOIN master_table and daily_table.

This should help for you: How can I do an UPDATE statement with JOIN in SQL?

Should be something like this:

UPDATE daily_table dt
INNER JOIN master_table mt ON
    mt.col = dt.col
SET dt.taskdate = 
CASE
    WHEN mt.check_day = 'Today' THEN CONCAT(CURDATE(),'   ',mt.task_time)          
    WHEN mt.check_day = 'Tomorrow' THEN CONCAT(   DATE_ADD(CURDATE(),INTERVAL 1 DAY) ,' ', mt.task_time)
END

concatenate curdate() with time based on another table

1 Answers1