join in SQL update statement

I had to update value from a staging table( this might be a temp table ) into another database table. Here is how I did it using a inner join in the Update statement.

UPDATE Table1
SET Table1.Field1 = StagingTable.Field1
FROM Table1 INNER JOIN StagingTable ON Table1.Field2 = StagingTable.Field2

You can also add a where clause to this SQL:

WHERE StagingTable.Field3 IS NOT NULL

---------------------------------------------------------------------

Same way we can use JOINs in insert and delete statements

Print posted @ Monday, January 05, 2009 11:33 AM

Feedback

# re: join in SQL update statement

Thanks - This is just what I needed. 7/28/2009 11:44 AM | Linda

# re: join in SQL update statement

Just what I needed.

Thanks 7/29/2009 8:35 PM | Phil

# re: join in SQL update statement

Teşekkür edrim. Çok işime yaradı..
(Thanks. This help me too much) 7/30/2009 3:14 AM | Sermin

# re: join in SQL update statement

I hate to say it, but this doesn't work. Using the exact same format as your example, the error "Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'From'." is displayed.

I want to update the field HHTable.Value and change it to the value in field Table2.NewValue if the field HHTable.Keyfield is equal to the field Table2.Row.

Any suggestions on what I need to do differently to this query above? 9/28/2009 10:47 AM | Matt

# re: join in SQL update statement

Thanks, just what I needed. 11/4/2009 5:16 AM | Kadhim

faizan ahmad