Home » SQL & PL/SQL » SQL & PL/SQL » want correct query
want correct query [message #280016] Mon, 12 November 2007 05:42 Go to next message
mrkcse
Messages: 62
Registered: November 2007
Location: Mumbai,India
Member

update temp2 set dt=(select edate from temp1 where temp2.cat!=temp1.adcat and rownum=1 and temp2.scode=temp1.bcode);


by using this iam getting the result but already existing rows in temp2 table which are not satisfying where class are getting empty dt columns.

anyone please help me to restrict the change of the dt column which does not satisfes the condition in where clause.

[Updated on: Mon, 12 November 2007 05:50]

Report message to a moderator

Re: want correct query [message #280022 is a reply to message #280016] Mon, 12 November 2007 05:57 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

Quote:

and rownum=1
can you elobrate on this.what exactly is this meant for?i don't see any reason for including this.


regards,
Re: want correct query [message #280024 is a reply to message #280016] Mon, 12 November 2007 06:02 Go to previous messageGo to next message
mrkcse
Messages: 62
Registered: November 2007
Location: Mumbai,India
Member
because of multiple results from nested query for single result dt it has given error so by including rownum ,it allowed and got the right result but other records dt is also getting effected.
Re: want correct query [message #280029 is a reply to message #280016] Mon, 12 November 2007 06:06 Go to previous messageGo to next message
mrkcse
Messages: 62
Registered: November 2007
Location: Mumbai,India
Member
anyway my actual requirement is based upon the where clause(using temp1 and temp2) temp2 dt value should be changed and other dt values of records of temp2 which are not satisying the where clause should be remain same. Instead they are becoming empty.
Re: want correct query [message #280031 is a reply to message #280024] Mon, 12 November 2007 06:07 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member

can you just post a test case of your data for better understanding.and don't forget to format your code.


regards,
Re: want correct query [message #280036 is a reply to message #280016] Mon, 12 November 2007 06:13 Go to previous messageGo to next message
mrkcse
Messages: 62
Registered: November 2007
Location: Mumbai,India
Member
temp1:

SCODE VARCHAR2(10)
SRCODE VARCHAR2(20)
AD CHAR(1)
REF VARCHAR2(20)
EDATE DATE

temp2:

BCD VARCHAR2(10)
CAT CHAR(1)
SAM VARCHAR2(10)
DT DATE




after executing the query


BCD C SAM DT
---------- - ---------- ---------
123 B osdf 12-DEC-05
222 C sdf
123 B osdf 12-DEC-05

for BCD=122 second row it is not satisfying the condition and dt is empty
Re: want correct query [message #280038 is a reply to message #280016] Mon, 12 November 2007 06:16 Go to previous messageGo to next message
mrkcse
Messages: 62
Registered: November 2007
Location: Mumbai,India
Member
before ecexuting temp1 values are:

SCODE SRCODE A REF EDATE
---------- -------------------- - -------------------- ---------
123 345 C 1232 12-DEC-05
466 234 A 54 12-DEC-05
Re: want correct query [message #280066 is a reply to message #280016] Mon, 12 November 2007 07:10 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

update temp2 set dt=(select edate from temp1 where temp2.cat!=temp1.adcat and rownum=1 and temp2.scode=temp1.bcode)
AND EXISTS (select NULL from temp1 where temp2.cat!=temp1.adcat and rownum=1 and temp2.scode=temp1.bcode);


Thumbs Up
Rajuvan
Re: want correct query [message #280251 is a reply to message #280016] Tue, 13 November 2007 00:00 Go to previous messageGo to next message
mrkcse
Messages: 62
Registered: November 2007
Location: Mumbai,India
Member
thanks rajuvan,

we can use this query also for that.

update temp2 set dt=(select edate from temp1 where temp2.cat!=temp1.adcat and rownum=1 and temp2.scode=temp1.bcode)
WHERE EXISTS (select 1 from temp1 where temp2.cat!=temp1.adcat and rownum=1 and temp2.scode=temp1.bcode);
Re: want correct query [message #280343 is a reply to message #280016] Tue, 13 November 2007 05:05 Go to previous message
mrkcse
Messages: 62
Registered: November 2007
Location: Mumbai,India
Member
can anyone tell me the simplified form of the above query because iam using this query in vb6.0 it is taking so much time.
Previous Topic: tuples
Next Topic: Date Query
Goto Forum:
  


Current Time: Mon Dec 05 13:20:23 CST 2016

Total time taken to generate the page: 0.06121 seconds