From: Richard Huxton <dev@archonet.com>
To: Nicholas I <nicholas.domnic.i@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Need help on update.
Date: Thu, 21 Oct 2010 09:10:05 +0100
Message-ID: <4CBFF55D.1060200@archonet.com> (raw)
In-Reply-To: <AANLkTi=nyWz=SNYZ+uLNPeUKOTgSscVqZR6CRTUkL99z@mail.gmail.com>
References: <AANLkTi=nyWz=SNYZ+uLNPeUKOTgSscVqZR6CRTUkL99z@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, nicholas.domnic.i@gmail.com
Subject: Re: Need help on update.
In-Reply-To: <4CBFF55D.1060200@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox