Thread: Need help on update.

Hi, <br /><br />there are two tables, table1 and table2, each having same column name called sn_no,name. i want to
updatetable1 names with table2 where sn_no 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 /><br /><b>UPDATE table1 SET name
=(select name from table2) where table2.sn_no = table1.sn_no;</b><br />ERROR:  missing FROM-clause entry for table
"table2"<br/>LINE 1: ...table1 SET name = (select name from table2) where table2.sn_.<br /><br /><b>UPDATE table1 inner
jointable2 on table2.sn_no = table1.sn_no set <a href="http://table2.name">table2.name</a> = <a
href="http://table1.name">table1.name</a>;</b><br/>ERROR:  syntax error at or near "inner"<br /> LINE 1: UPDATE table1
innerjoin table2 on table2.sn_no = table1.sn_...<br /><br />-Nicholas I<br /><br /><br /> 

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

that was amazing, it worked thanks a lot.<br /><br />-Nicholas I<br /><br /><div class="gmail_quote">On Thu, Oct 21,
2010at 1:40 PM, Richard Huxton <span dir="ltr"><<a href="mailto:dev@archonet.com">dev@archonet.com</a>></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
aretwo tables, table1 and table2, each having same column name<br /> called sn_no,name. i want to update table1 names
withtable2 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 *
fromtable2;<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,'ramnad'),(2,'bangalore'),(3,'chennai');<br /> INSERT INTO table2 VALUES
(1,'Hyderabad'),(2,'Delhi'),(3,'Bombay');<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
(table1in 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 />