refer to outside field value in subselect?
I want to do a query to update values that I forgot to copy over in a mass insert. However I'm not sure how to phrase it.
UPDATE table
SET text_field_1 = (SELECT text_field_2
FROM other_table
WHERE id = **current row in update statement, outside parens**.i开发者_如何学JAVAd )
How do I do this? It seems like a job for recursion.
Use:
UPDATE YOUR_TABLE
SET text_field_1 = (SELECT t.text_field_2
FROM other_table t
WHERE t.id = YOUR_TABLE.id)
Warning
If there's no supporting record in other_table
, text_field_1
will be set to NULL.
Explanation
In standard SQL, you can't have table aliases on the table defined for the UPDATE (or DELETE) statement, so you need to use full table name to indicate the source of the column.
It's called a correlated subquery -- the correlation is be cause of the evaluation against the table from the outer query.
Clarification
MySQL (and SQL Server) support table aliases in UPDATE and DELETE statement, in addition to JOIN syntax:
UPDATE YOUR_TABLE a
JOIN OTHER_TABLE b ON b.id = a.id
SET a.text_field_1 = b.text_field_2
...is not identical to the provided query, because only the rows that match will be updated -- those that don't match, their text_field_1
values will remain untouched. This is equivalent to the provided query:
UPDATE YOUR_TABLE a
LEFT JOIN OTHER_TABLE b ON b.id = a.id
SET a.text_field_1 = b.text_field_2
If there is one ID field:
UPDATE updtable t1
SET t1.text_field_1 = (
SELECT t2.text_field_2
FROM seltable t2
WHERE t1.ID = t2.ID
)
;
UPDATE Table1, Tabl2
SET Table1.myField = Table2.SomeField
WHERE Table1.ID = Table2.ID
Note: I have not tried it.
This will only update records where IDs match.
Try this:
UPDATE table
SET text_field_1 = (SELECT text_field_2
FROM other_table
WHERE id = table.id )
精彩评论