that was amazing, it worked thanks a lot. -Nicholas I
On Thu, Oct 21, 2010 at 1:40 PM, Richard Huxton <d...@archonet.com> wrote: > 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 >
