[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-sql
Subject:    Re: [SQL] Need help on update.
From:       Nicholas I <nicholas.domnic.i () gmail ! com>
Date:       2010-10-21 8:31:20
Message-ID: AANLkTikuKDCBujnR=9f9miJDgsY7pHmD0MfqAsk5=yPy () mail ! gmail ! com
[Download RAW message or body]

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
>

[Attachment #3 (text/html)]

that was amazing, it worked thanks a lot.<br><br>-Nicholas I<br><br><div \
class="gmail_quote">On Thu, Oct 21, 2010 at 1:40 PM, Richard Huxton <span \
dir="ltr">&lt;<a href="mailto:dev@archonet.com">dev@archonet.com</a>&gt;</span> \
wrote:<br> <blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; \
border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;"><div class="im">On \
21/10/10 08:43, Nicholas I wrote:<br> <blockquote class="gmail_quote" style="margin: \
0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;"> \
Hi,<br> <br>
there are two tables, table1 and table2, each having same column name<br>
called sn_no,name. i want to update table1 names with table2 where sn_no<br>
are same.<br>
<br>
select * from table1;<br>
sn_no |   name<br>
-------+-----------<br>
      1 | ramnad<br>
      2 | bangalore<br>
      3 | chennai<br>
<br>
<br>
select * from table2;<br>
  sn_no |   name<br>
-------+-----------<br>
      1 | Hyderabad<br>
      2 | Delhi<br>
      3 | Bombay<br>
<br>
Any help ?<br>
<br>
I tried with , some of the queries like,<br>
</blockquote>
<br></div>
Close. This is surprisingly difficult in standard SQL. PostgreSQL has a \
(non-standard) FROM clause you can use though.<br> <br>
BEGIN;<br>
<br>
CREATE TABLE table1 (sn int, nm text);<br>
CREATE TABLE table2 (sn int, nm text);<br>
INSERT INTO table1 VALUES \
(1,&#39;ramnad&#39;),(2,&#39;bangalore&#39;),(3,&#39;chennai&#39;);<br> INSERT INTO \
table2 VALUES (1,&#39;Hyderabad&#39;),(2,&#39;Delhi&#39;),(3,&#39;Bombay&#39;);<br> \
<br> UPDATE table1 SET nm = table2.nm<br>
FROM table2<br>
WHERE <a href="http://table1.sn" target="_blank">table1.sn</a> = <a \
href="http://table2.sn" target="_blank">table2.sn</a>;<br> <br>
SELECT * FROM table1;<br>
<br>
ROLLBACK;<br>
<br>
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.<br><font \
color="#888888"> <br>
-- <br>
  Richard Huxton<br>
  Archonet Ltd<br>
</font></blockquote></div><br>



[prev in list] [next in list] [prev in thread] [next in thread]