Home » SQL & PL/SQL » SQL & PL/SQL » Update Query
Update Query [message #217502] Fri, 02 February 2007 10:02 Go to next message
rahul.priyadarshy
Messages: 28
Registered: December 2005
Junior Member
Hi All,

Below is the scenario for which i need update query.

SQL> select * from t3;

ORDER_NO P_ID C_ID
---------- ---------- ----------
1 p1 1
2 p1 1
3 p2 c3
4 p4 c4
5 p5 c5

SQL> select * from t4;

P_ID C_ID
---------- ----------
p1 1
p2 c2
p3 c3
p4
p5 5

we have two tables t3 and t4. we have to update only those values for t3.c_id from t4.c_id for which p_id is matching between two tables but c_id is not matching.

Regards

Rahul Priyadarshy
Re: Update Query [message #217504 is a reply to message #217502] Fri, 02 February 2007 10:04 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
1st write a SELECT that p_id is matching between two tables but c_id is not matching.
Re: Update Query [message #217505 is a reply to message #217502] Fri, 02 February 2007 10:09 Go to previous messageGo to next message
rahul.priyadarshy
Messages: 28
Registered: December 2005
Junior Member
1* update t3 set t3.c_id=(select t4.c_id from t4,t3 t33 where t3.p_id=t4.p_id and t3.c_id<>t4.c_id
SQL> /

5 rows updated.

Its updating all the rows but ideally is should only update 2 rows as below query is showing .

SQL>select t3.ORDER_NO,t3.P_ID,t3.C_ID from t3,t4 where t3.p_id=t4.p_id and t3.c_id<>t4.c_id


ORDER_NO P_ID C_ID
---------- ---------- ----------
3 p2 c3
5 p5 c5
Re: Update Query [message #217508 is a reply to message #217502] Fri, 02 February 2007 10:15 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
>Its updating all the rows but ideally is should only update 2 rows as below query is showing .
That is because there is no WHERE clause on the UPDATE, so of course it does all the rows.
Re: Update Query [message #217553 is a reply to message #217502] Fri, 02 February 2007 14:40 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member
SQL> select * from foo1;

ORDER_NO P_ID C_ID
---------- ---- ----
1 p1 1
2 p1 1
3 p2 c3
4 p4 c4
5 p5 c5

SQL> select * from foo2;

P_ID C_ID
---- ----
p1 1
p2 c2
p3 c3
p4
p5 5

SQL> update(
2 select foo2.c_ID f2src ,foo1.C_ID f1dest
3 from foo1,foo2 where
4 foo1.p_id=foo2.p_id and foo1.c_id<>foo2.c_id)
5 set f1dest=f2src
6 ;

2 rows updated.

SQL>

SQL> select * from foo1;

ORDER_NO P_ID C_ID
---------- ---- ----
1 p1 1
2 p1 1
3 p2 c2
4 p4 c4
5 p5 5

==========

foo2 should have a pk or uk on P_ID.

Srini

Re: Update Query [message #217732 is a reply to message #217553] Mon, 05 February 2007 02:57 Go to previous message
rahul.priyadarshy
Messages: 28
Registered: December 2005
Junior Member
Thanks Srini its working. Thanks a lot...
Previous Topic: related to drop command
Next Topic: concatenate field results
Goto Forum:
  


Current Time: Mon Dec 05 10:40:31 CST 2016

Total time taken to generate the page: 0.04931 seconds