Re: Need help on update. - Mailing list pgsql-sql

On 21/10/10 08:43, Nicholas I wrote:

Hi,

there are two tables, table1 and table2, each having same column name
called sn_no,name. i want to update table1 names with table2 where sn_no
are same.

select * from table1;
sn_no |   name
-------+-----------
     1 | ramnad
     2 | bangalore
     3 | chennai


select * from table2;
 sn_no |   name
-------+-----------
     1 | Hyderabad
     2 | Delhi
     3 | Bombay

Any help ?

I tried with , some of the queries like,

Close. This is surprisingly difficult in standard SQL. PostgreSQL has a (non-standard) FROM clause you can use though.

BEGIN;

CREATE TABLE table1 (sn int, nm text);
CREATE TABLE table2 (sn int, nm text);
INSERT INTO table1 VALUES (1,'ramnad'),(2,'bangalore'),(3,'chennai');
INSERT INTO table2 VALUES (1,'Hyderabad'),(2,'Delhi'),(3,'Bombay');

UPDATE table1 SET nm = table2.nm
FROM table2
WHERE table1.sn = table2.sn;

SELECT * FROM table1;

ROLLBACK;

Be careful with aliasing the target of the update (table1 in this case). As another poster has discovered, that counts as another table in your join.

--
 Richard Huxton
 Archonet Ltd