Home » SQL & PL/SQL » SQL & PL/SQL » update using subquery (10g)
update using subquery [message #443769] Wed, 17 February 2010 04:54 Go to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Hi,

I have a situation, my update query seems to fail with error
ORA-00907: missing right parenthesis


Below is my test case, any guidance will be much appreaciated

create table t1(
 sn varchar2(30),
 inv_org number);

create table s1(
 sn varchar2(30),
 trx_date date,
 inv_org number);
 

insert into t1 (sn) values ('11111');
insert into t1 (sn) values ('22222');
insert into s1 values ('11111','3-Jan-2008','111');
insert into s1 values ('11111','1-Jan-2008','222');
insert into s1 values ('11111','2-Jan-2008','333');
insert into s1 values ('22222','6-Jan-2008','444');
insert into s1 values ('22222','7-Jan-2008','555');
insert into s1 values ('22222','5-Jan-2008','666');
commit;

update t1 tst  
set tst.inv_org = 
(select inv_org 
from s1 
where tst.sn = s1.sn
 and rownum = 1
 and sn = '22222' 
order by trx_date asc)
where sn = '22222'; 

[Updated on: Wed, 17 February 2010 04:59] by Moderator

Report message to a moderator

Re: update using subquery [message #443772 is a reply to message #443769] Wed, 17 February 2010 05:02 Go to previous messageGo to next message
Michel Cadot
Messages: 63817
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Subquery in SET should return only one row and so ORDER BY is irrelevant, above all when you speficy "where rownum=1".

Now one can fix your query without knowing what you are trying to do. So explain it.

Regards
Michel
Re: update using subquery [message #443788 is a reply to message #443772] Wed, 17 February 2010 05:44 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
thank you for your reply

I am trying to accomplish the following.

1) i want to update a target table
2) the source table will contain many matching records for the matching key, in this case is sn column
3) so i will pick only one record from source table, the 1 record will have the latest trx_date. that is the reason i am filtering by rownum = 1 and also with order clause.

Is there any way i could achieve the above
Re: update using subquery [message #443792 is a reply to message #443769] Wed, 17 February 2010 06:12 Go to previous messageGo to next message
cookiemonster
Messages: 12321
Registered: September 2008
Location: Rainy Manchester
Senior Member
Order by is always applied after the where clause. If you want to do a top-n query then this:
select inv_org 
from s1 
where tst.sn = s1.sn
 and rownum = 1
 and sn = '22222' 
order by trx_date asc
should be:
select inv_org from (
select inv_org 
from s1 
where tst.sn = s1.sn
and sn = '22222' 
order by trx_date asc)
where rownum = 1


Also if you want to use the record with the latest trx_date you should be ordering DESC not ASC.
You should probably also think about what would happen if you had two rows with the same latest date.
Re: update using subquery [message #443808 is a reply to message #443792] Wed, 17 February 2010 06:56 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
thank you for the help.

yes, i will have to think of below
You should probably also think about what would happen if you had two rows with the same latest date.


thank you again
Re: update using subquery [message #443822 is a reply to message #443769] Wed, 17 February 2010 07:56 Go to previous messageGo to next message
joy_division
Messages: 4618
Registered: February 2005
Location: East Coast USA
Senior Member
ajitpal.s wrote on Wed, 17 February 2010 05:54


create table s1(
 sn varchar2(30),
 trx_date date,
 inv_org number);
 
insert into s1 values ('11111','3-Jan-2008','111');
insert into s1 values ('11111','1-Jan-2008','222');
insert into s1 values ('11111','2-Jan-2008','333');
insert into s1 values ('22222','6-Jan-2008','444');
insert into s1 values ('22222','7-Jan-2008','555');
insert into s1 values ('22222','5-Jan-2008','666');


2 of these columns are improperly INESERTed.
Re: update using subquery [message #443824 is a reply to message #443792] Wed, 17 February 2010 08:00 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
hi,

after my testing results on the test case above, is there any way i could replace the value with a default value if no records are returned

i tried the using the method below, but it did not work
select nvl(inv_org,'n/a') from (
select inv_org 
from s1
where sn = '33333' 
order by trx_date)
where rownum = 1

[Updated on: Wed, 17 February 2010 08:02]

Report message to a moderator

Re: update using subquery [message #443834 is a reply to message #443769] Wed, 17 February 2010 08:38 Go to previous messageGo to next message
cookiemonster
Messages: 12321
Registered: September 2008
Location: Rainy Manchester
Senior Member
select nvl(select inv_org from (
select inv_org 
from s1
where sn = '33333' 
order by trx_date)
where rownum = 1),'n/a') from dual;


However in the update all you need is:
UPDATE table
SET column = nvl(<subquery>, 'n/a')

Don't need dual for the update.

[Updated on: Wed, 17 February 2010 08:38]

Report message to a moderator

Re: update using subquery [message #443837 is a reply to message #443834] Wed, 17 February 2010 09:04 Go to previous message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
thank you again, great..that really helps!!
Previous Topic: how to increment count when GROUP by is used
Next Topic: function raise no error when call in select.
Goto Forum:
  


Current Time: Fri Sep 30 17:28:23 CDT 2016

Total time taken to generate the page: 0.28083 seconds