Home » SQL & PL/SQL » SQL & PL/SQL » simplify this query
simplify this query [message #280672] Wed, 14 November 2007 08:19 Go to next message
mrkcse
Messages: 62
Registered: November 2007
Location: Mumbai,India
Member
this query is taking so much time to excecute

update temp2 set dt=(select COALESCE(edate,temp2.dt) from temp1 where temp2.cat!=temp1.ad and rownum=1 and temp2.bcd=temp1.scode and temp2.pcode=temp2.pncode and temp2.dt=null);


coalesce() is for restricting null values to be updated in temp2.



[Updated on: Wed, 14 November 2007 08:20]

Report message to a moderator

Re: simplify this query [message #280676 is a reply to message #280672] Wed, 14 November 2007 08:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Could you post this in a formatted way and not in a single string of 300 characters long?

Regards
Michel
Re: simplify this query [message #280898 is a reply to message #280672] Thu, 15 November 2007 02:43 Go to previous messageGo to next message
mrkcse
Messages: 62
Registered: November 2007
Location: Mumbai,India
Member
update temp2 set dt=(select COALESCE(edate,temp2.dt) from temp1

where temp2.cat!=temp1.ad and rownum=1 and temp2.bcd=temp1.scode and temp2.pcode=temp2.pncode and temp2.dt IS NULL);

[Updated on: Thu, 15 November 2007 02:47]

Report message to a moderator

Re: simplify this query [message #280901 is a reply to message #280898] Thu, 15 November 2007 02:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Well, now a little bit more effort:
- indent
- one line per condition
...

OraFAQ Forum Guide, Section "How to format your post?", SQL Formatter
Always useful tro read the basics.

Regards
Michel
Re: simplify this query [message #280905 is a reply to message #280901] Thu, 15 November 2007 02:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This gives:
UPDATE temp2
SET    dt = (SELECT Coalesce(eDate,temp2.dt)
             FROM   temp1
             WHERE  temp2.Cat != temp1.Ad
             AND ROWNUM = 1
             AND temp2.bcd = temp1.sCode
             AND temp2.pCode = temp2.pnCode
             AND temp2.dt = NULL);

And we immediatly see:
- you update the whole table
- rownum=1 means wrong design or coding
- coalesce is there equivalent to NVL, better use this one.

Regards
Michel
Re: simplify this query [message #280906 is a reply to message #280901] Thu, 15 November 2007 02:58 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
not only that: you select records where temp2.dt IS NULL. Now look at your coalesce. What's the use?

MHE
Re: simplify this query [message #280907 is a reply to message #280906] Thu, 15 November 2007 03:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, I forgot, with "temp2.dt = NULL" you set all dt to NULL.

In short, your statement is equivalent to:
UPDATE temp2 set dt = NULL;

Regards
Michel
Re: simplify this query [message #280908 is a reply to message #280672] Thu, 15 November 2007 03:02 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Quote:

coalesce() is for restricting null values to be updated in temp2.


Wrong ..

The Temp2.dt will be Updated If the correlated subquery fails to satisfy the condition Even if you are using COALESCE. Temp2.dt is Updated NUll Not Only because of Null values is returned by the subquery also because of not meeting condition in the Subquery.

NB : Pls Check for the Proper indexing of the Respective fiels for Faste result and EXISTS clasue is highly recommented as i suggested earlier.

Thumbs Up
Rajuvan
Re: simplify this query [message #280910 is a reply to message #280672] Thu, 15 November 2007 03:04 Go to previous messageGo to next message
mrkcse
Messages: 62
Registered: November 2007
Location: Mumbai,India
Member
but i got the result...so just want to know if there is any simplified format because iam using this query in vb6.0
Re: simplify this query [message #280912 is a reply to message #280910] Thu, 15 November 2007 03:06 Go to previous messageGo to next message
mrkcse
Messages: 62
Registered: November 2007
Location: Mumbai,India
Member
but using exists again query becomes long anyway i will try to frame this query again.
Re: simplify this query [message #280916 is a reply to message #280910] Thu, 15 November 2007 03:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

but i got the result

A wrong result.

Quote:

want to know if there is any simplified format

Yes, have a look at my previous query.

Regards
Michel
Re: simplify this query [message #280922 is a reply to message #280672] Thu, 15 November 2007 03:20 Go to previous messageGo to next message
mrkcse
Messages: 62
Registered: November 2007
Location: Mumbai,India
Member
yes michel iam sorry i have checked once again it is giving wrong result as u said.so would u suggest me to change any thing please tell me.

my req is to update the dt column which are empty with the date of edate of temp1
based on the conditions i have specified in where clause
at the same time null values should not be assigned to non satisfying dt fields of temp2.

[Updated on: Thu, 15 November 2007 03:36]

Report message to a moderator

Re: simplify this query [message #280931 is a reply to message #280672] Thu, 15 November 2007 04:02 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member



Quote:

my req is to update the dt column which are empty with the date of edate of temp1



UPDATE temp2
SET    dt = (SELECT eDate 
             FROM   temp1
             WHERE  temp2.Cat != temp1.Ad
             AND ROWNUM = 1
             AND temp2.bcd = temp1.sCode
             AND temp2.pCode = temp2.pnCode)
WHERE temp2.dt IS NULL;


Have a nice luck. ( I am not bothered on the conditions you menationed)

Thumbs Up
Rajuvan
Re: simplify this query [message #280932 is a reply to message #280931] Thu, 15 November 2007 04:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Still afraid about the "rownum=1".
If it is necessary then result is random.

Regards
Michel
Re: simplify this query [message #280943 is a reply to message #280672] Thu, 15 November 2007 04:17 Go to previous messageGo to next message
mrkcse
Messages: 62
Registered: November 2007
Location: Mumbai,India
Member
rajavu1 thanks its working in sql i have to check it with vb6.0
Re: simplify this query [message #280945 is a reply to message #280905] Thu, 15 November 2007 04:19 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Just to mention; in the original message, query looks like
UPDATE temp2 SET ...
WHERE temp2.dt = NULL;             --> this is wrong!

It should be "temp2.dt IS NULL", just as Rajavu wrote it in his message.
Re: simplify this query [message #280952 is a reply to message #280672] Thu, 15 November 2007 04:27 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Even my last query will update temp2.dt to NULL when ,

1. The subquer fails ( ie, Not matching sCode in temp1 etc... )
2. edate is NULL for the satisfied subquery .

BTW : Do u really mean AND temp2.pCode = temp2.pnCode in the Sudquery ? ( Both field from temp2)
If so It is better to change the query to ..

UPDATE temp2
SET    dt = (SELECT eDate 
             FROM   temp1
             WHERE  temp2.Cat != temp1.Ad
             AND ROWNUM = 1
             AND temp2.bcd = temp1.sCode)
WHERE temp2.dt IS NULL
AND temp2.pCode = temp2.pnCode;

[Updated on: Thu, 15 November 2007 04:29]

Report message to a moderator

Re: simplify this query [message #280959 is a reply to message #280672] Thu, 15 November 2007 04:43 Go to previous message
mrkcse
Messages: 62
Registered: November 2007
Location: Mumbai,India
Member
thanks for your suggestion rajuvan...iam very much pleased with your way of explaining..
Previous Topic: how to get this data in a one line of output
Next Topic: Import Excel File into an SQL Table
Goto Forum:
  


Current Time: Wed Dec 07 10:33:45 CST 2016

Total time taken to generate the page: 0.12303 seconds