CODEWITHSUNDEEP
sqlamazon-redshift
Jagan
Jagan

Reputation: 477

Redshift: Update one table using another table with JOIN vs without JOIN

I want to update some columns of tableA with corresponding values from tableB.

Are the following two update queries equivalent? (Does the first query use an implicit JOIN?)

Query 1 (without explicit JOIN):

UPDATE tableA
SET col1 = tb.col1, col2 = tb.col2
FROM tableB tb
WHERE tb.col3 = tableA.col3

Query 2 (with explicit JOIN):

UPDATE tableA
SET col1 = tb.col1, col2 = tb.col2
FROM tableB tb JOIN tableA ta ON ta.col3=tb.col3

Upvotes: 0

Views: 301

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

The tables are not equivalent.

The second query has two separate references to TableA. There is no correlation clause between the tableA in the update and the FROM clause. So, every row is going to attempt to be updated with the all the rows that result from the FROM clause.

No doubt, you intend the first version.

Also note that in SQL Server, the two would be equivalent -- even with the ta table alias. SQL Server ignores that.

Upvotes: 1

Related Questions