Update a set of Columns with criteria based on another table?
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_开发者_StackOverflow1.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 ...'
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>';
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>'
精彩评论