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 )