CODEWITHSUNDEEP
sqlsql-updateinner-joinamazon-redshift
Rohit Mishra
Rohit Mishra

Reputation: 281

Update on INNER JOIN not working

I am trying to update a table in Redshift from another table by using inner join.

Query:

UPDATE target 
INNER JOIN updates ON target.name = updates.name and target.title = updates.title and target.age = updates.age 
  SET target.num_hit_ratio = updates.num_hit_ratio, target.plan_id = updates.plan_id;

This is giving error:

syntax error at or near "INNER"

Where am I making mistake?

Upvotes: 0

Views: 4371

Answers (3)

bma
bma

Reputation: 9756

As noted in the Redshift documentation, an UPDATE can join to other tables to generate the values in the SET command, but the join syntax is in the form of WHERE clause predicates (not explicit JOIN syntax).

UPDATE target
  SET target.num_hit_ratio = updates.num_hit_ratio,
      target.plan_id = updates.plan_id
FROM updates 
WHERE target.name = updates.name 
AND target.title = updates.title 
AND target.age = updates.age

Note this alternative WHERE clause syntax, by removing the AND keywords and wrapping the conditions in brackets:

...
WHERE
  (target.name, target.title, target.age)
  =
  (updates.name, updates.title, updates.age)

Upvotes: 3

DarkMark
DarkMark

Reputation: 129

Using your query, you would just need to rearrange a few things as follows:

UPDATE
    target
SET
    target.num_hit_ratio = updates.num_hit_ratio, target.plan_id = updates.plan_id
FROM
    target
    JOIN updates ON target.name = updates.name AND target.title = updates.title AND target.age = updates.age

Personally I would give your tables an alias, to make it easier to type (because I'm lazy :P):

UPDATE
    T
SET
    num_hit_ratio = U.num_hit_ratio,
    plan_id = U.plan_id
FROM
    target T
    JOIN updates U ON T.name = U.name AND T.title = U.title AND T.age = U.age

Upvotes: 0

F. Lins
F. Lins

Reputation: 636

You can use aliases too, that way your joins are more readable:

UPDATE t
SET ...
FROM target t 
INNER JOIN updates u ON t.name = u.name AND t.title = u.title AND t.age = u.age

Upvotes: 0

Related Questions