Updating records in Postgres using FROM clause

Question

I'm changing my db schema, and moving column 'seat' from old_table to new_table. First I added a 'seat' column to new_table. Now I'm trying to populate the column with the values from old_table.

UPDATE new_table
SET seat = seat
FROM old_table
WHERE old_table.id = new_table.ot_id;

This returns ERROR: column reference "seat" is ambiguous.

UPDATE new_table nt
SET nt.seat = ot.seat
FROM old_table ot
WHERE ot.id = nt.ot_id;

Returns ERROR: column "nt" of relation "new_table" does not exist

Ideas?

I think this is not specific to PostgreSQL, but standard SQL — leonbloy
– leonbloy, Commented May 3, 2010 at 18:56

clee · Accepted Answer · 2010-05-03 18:53:04Z

3

UPDATE new_table
SET seat = old_table.seat
FROM old_table
WHERE old_table.id = new_table.ot_id;

answered May 3, 2010 at 18:53

clee

11.2k6 gold badges38 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

leonbloy · Accepted Answer · 2010-05-03 18:53:28Z

1

IIRC the table to be updated is the one that must not be aliased. Did you try this?

UPDATE new_table
SET seat = ot.seat
FROM old_table ot
WHERE ot.id = ot_id;

answered May 3, 2010 at 18:53

leonbloy

76.5k22 gold badges149 silver badges198 bronze badges

Comments

nos · Accepted Answer · 2010-05-03 18:56:26Z

0

You should be able to just mention seat, like so

UPDATE new_table nt
SET seat = ot.seat
FROM old_table ot
WHERE ot.id = nt.ot_id;

answered May 3, 2010 at 18:56

nos

231k60 gold badges436 silver badges516 bronze badges

Collectives™ on Stack Overflow

Updating records in Postgres using FROM clause

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related