Update a set of Columns with criteria based on another table?

Question

Hi Intellects,

I've two tables :

table_1 - ControlID, Code, ReportedDate, FiledDate Age, AgeCategory, etc.,

table_2 - ControlID, Code, ReportedDate, FiledDate etc.,

ControlID in table_1 is Foreign key whereas not in table_2. I need to update ReportedDate in table_1 with ReportedDate in table_2 and Age and AgeCatogory has been calculated and fine.

I want to update those three columns in table_1, where ControlID, FiledDate and Code in both are identical.

Now far I've :

UPDATE table_1 SET ReportedDate=table_2.ReportedDate, Age='<value>' AgeCategory='<value>'
         WHERE table_1.ControlID=table_2.ControlID AND
         table_1.FiledDate=table_2.FiledDate AND table_1.Code=table_2.Code

If anyone has the idea of how could it be resolved???

Anyhelp would be appreciated...

EDIT:

I'm getting error saying MySQL Syntax error at 'FROM ...'

Answer 1

There is no FROM allowed within the UPDATE syntax 1:

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
    SET col_name1=expr1 [, col_name2=expr2 ...]
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

If you like to fetch content from a second table, you might want to use a subquery.

Try this code:

UPDATE table_1 SET ReportedDate=
    (SELECT ReportedDate FROM table_2
      WHERE table_1.ControlID = table_2.ControlID
      AND table_1.Code = table_2.Code
    ), Age='<value>' AgeCategory='<value>'

Answer 2

    UPDATE table_1
    JOIN table_2 
    ON table_1.ControlID=table_2.ControlID
         AND table_1.FiledDate=table_2.FiledDate
         AND table_1.Code=table_2.Code
    SET table_1.ReportedDate=table_2.ReportedDate, 
         table_1.Age='<value>',
         table_1.AgeCategory='<value>';