Re: Need help on update.

that was amazing, it worked thanks a lot.

-Nicholas I

On Thu, Oct 21, 2010 at 1:40 PM, Richard Huxton <dev(at)archonet(dot)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
>