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.
. Field1 = StagingTable . Field1
INNER JOIN StagingTable
ON Table1 . Field2 = StagingTable . Field2
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.
Table1 TB1 INNERJOIN
ON Table1.Field2 = StagingTable.Field2
Update 1: Following is the MySQL syntax (Thanks Evan)
table1. field1 = table2.field2
table1.field3 = table2.field4
This did not work for me in Oracle, had to use sub queries
Following is the teradata way:
SET TargetTableField = SourceTable.SourceTableField
WHERE TargetTable.Field = SourceTable.Field; -- join on expression will come here