pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedNeed help on update.
3+ messages / 2 participants
[nested] [flat]
* Need help on update.
@ 2010-10-21 07:43 Nicholas I <nicholas.domnic.i@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Nicholas I @ 2010-10-21 07:43 UTC (permalink / raw)
To: pgsql-sql
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,
*UPDATE table1 SET name = (select name from table2) where table2.sn_no =
table1.sn_no;*
ERROR: missing FROM-clause entry for table "table2"
LINE 1: ...table1 SET name = (select name from table2) where table2.sn_.
*UPDATE table1 inner join table2 on table2.sn_no = table1.sn_no set
table2.name = table1.name;*
ERROR: syntax error at or near "inner"
LINE 1: UPDATE table1 inner join table2 on table2.sn_no = table1.sn_...
-Nicholas I
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Need help on update.
@ 2010-10-21 08:10 Richard Huxton <dev@archonet.com>
parent: Nicholas I <nicholas.domnic.i@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Richard Huxton @ 2010-10-21 08:10 UTC (permalink / raw)
To: Nicholas I <nicholas.domnic.i@gmail.com>; +Cc: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Need help on update.
@ 2010-10-21 08:19 Nicholas I <nicholas.domnic.i@gmail.com>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 3+ messages in thread
From: Nicholas I @ 2010-10-21 08:19 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
that was amazing, it worked thanks a lot.
-Nicholas I
On Thu, Oct 21, 2010 at 1:40 PM, Richard Huxton <dev@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
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2010-10-21 08:19 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-10-21 07:43 Need help on update. Nicholas I <nicholas.domnic.i@gmail.com>
2010-10-21 08:10 ` Richard Huxton <dev@archonet.com>
2010-10-21 08:19 ` Nicholas I <nicholas.domnic.i@gmail.com>
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