CODEWITHSUNDEEP
sqlpostgresql
user373201
user373201

Reputation: 11455

update column in one table based on condition in another table

I am using postgresql. Both tables have the remarks column. When I run this i get an error stating column inv not found. 

UPDATE inventory SET INV.REMARKS = INV.REMARKS || ', $A'
FROM  priceguide_inventory pg, inventory INV
WHERE (INV.Lot_ID = pg.Lot_ID)
AND INV.Condition = 'New'

Upvotes: 0

Views: 1262

Answers (3)

udhaya kumar
udhaya kumar

Reputation: 169

UPDATE a SET Remarks=b.Remarks || ',$A' FROM inventory a JOIN priceguide_inventory b ON a.Lot_ID = b.Lot_ID WHERE a.condition='New'

Upvotes: 0

Mehrad Eslami
Mehrad Eslami

Reputation: 306

UPDATE inventory 
SET REMARKS = REMARKS || ', $A'
FROM  priceguide_inventory pg
WHERE inventory.Lot_ID = priceguide_inventory.Lot_ID AND 
      inventory.Condition = 'New'

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522712

Your syntax is off, try this instead:

UPDATE inventory AS inv
SET REMARKS = REMARKS || ', $A'
FROM priceguide_inventory AS pg
WHERE inv.Lot_ID = pg.Lot_ID AND
      inv.Condition = 'New'

Check the documentation for the update join synatax.

Upvotes: 1

Related Questions