Click here to Skip to main content
15,072,267 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
<br />
SQL> desc t1<br />
 Name         Null?    Type<br />
 -------------------------------<br />
 NO          NOT NULL NUMBER(38)<br />
 TID                  NUMBER(38)<br />
<br />
SQL> desc t2<br />
 Name          Null?    Type<br />
 ----------------------------------<br />
 TID           NOT NULL NUMBER(38)<br />
 NM                     CHAR(10)<br />
 NUM                    NUMBER(38)<br />
<br />
SQL> update t1,t2                         <br />
  2  SET t2.num=220333<br />
  3  where t1.tid=t2.tid<br />
  4  and t1.no=1<br />
  5  ;<br />


update t1,t2
*
ERROR at line 1:
ORA-00971: missing SET keyword

I want to update <num> column of t2 but using <no> column of table t1.
Posted
Updated 12-Feb-13 22:46pm
v2

1 solution

Try:
SQL
UPDATE  t2
SET     t2.num = 220333
WHERE   EXISTS(
    SELECT  1
    FROM    t1
    WHERE   t1.tid=t2.tid
    AND     t1.no=1
    )
   
Comments
surkhi 14-Feb-13 0:37am
   
Thanx for it...
So,I have to select the row number to which I want to modify in select statement(select 1,select 2,select3........)
right???
Jörgen Andersson 14-Feb-13 2:18am
   
No, the constant is only so that a row gets selected for the EXISTS clause. For an EXISTS to work you can select anything.
http://www.techonthenet.com/sql/exists.php
I prefer to select a constant instead of the standard * because I've been told it uses less memory. I have never controlled that statement though. Have checked it now, it doesn't matter, you can select whatever you want.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900