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
WHERE
StagingTable . Field3 IS NOT NULL
---------------------------------------------------------------------
Same way we can use JOINs in delete statements
Syntax for join in Delete is a bit different e.g.
DELETE
TB1
FROM
Table1 TB1 INNERJOIN
StagingTable
_ON Table1.Field2 = StagingTable._Field2
Update 1: Following is the MySQL syntax (Thanks Evan)
UPDATE
table1
INNER
JOIN projects
ON
table1. field1 = table2.field2
SET
table1.field3 = table2.field4
Update 2:
This did not work for me in Oracle, had to use sub queries
Update3: Teradata
Following is the teradata way:
UPDATE TargetTable FROM SourceTable SET TargetTableField = SourceTable.SourceTableField WHERE TargetTable.Field = SourceTable.Field; -- join on expression will come here