CODEWITHSUNDEEP
sqlpostgresqlsql-update
DUnkn0wn1
DUnkn0wn1

Reputation: 411

Update table column based on columns in other table

I have dim_table with the columns: 

item_key, client_id, date, notes, old_key

And a fact_table:

user_key, trans_key, item_key, ref_number,date

I have to update fact_table where item_key is NOT EQUAL to dim_table.item_key.
But dim_table.old_key is equal to the existing keys in fact_table.item_key.

I tried this, but it didn't work:

update fact_table
SET fact_table.item_key = dim_table.item_key
where
fact_table.item_key = dim_table.old_key
and fact_table.item_key <> dim_table.item_key

ERROR: missing FROM-clause entry for table "dim_table"

Upvotes: 0

Views: 85

Answers (2)

Brandon Spilove
Brandon Spilove

Reputation: 1569

You are missing the from part of your update statement:

update fact_table
SET item_key = dim_table.item_key
FROM dim_table
where
fact_table.item_key = dim_table.old_key
and fact_table.item_key <> dim_table.item_key

Upvotes: 2

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658887

  • Missing FROM item (as posted by @Brandon).
  • Column in the SET clause table-qualified, which is invalid (as commented by @Rohit).
  • Logic contradicts description:<> and = switched in WHERE clause.

I also simplified with table aliases:

UPDATE fact_table f
SET    item_key = d.item_key
FROM  dim_table d
WHERE  f.item_key <> d.old_key
AND    f.item_key = d.item_key;

Per documentation:

Do not include the table's name in the specification of a target column — for example, UPDATE tab SET tab.col = 1 is invalid.

Upvotes: 1

Related Questions