tags:

views:

50

answers:

3

Hi,

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?

+2  A: 
UPDATE new_table
SET seat = old_table.seat
FROM old_table
WHERE old_table.id = new_table.ot_id;
clee
+1  A: 

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;
leonbloy
A: 

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;
nos