Home » SQL & PL/SQL » SQL & PL/SQL » SQL query cleanup
SQL query cleanup [message #228707] Tue, 03 April 2007 18:43 Go to next message
kilyas
Messages: 24
Registered: March 2007
Junior Member
Hi,
I am working on a query to update the timeout for certain conditions. Now there are three tables which are involved in this particular query:


TESTFEEDPARAMS
TESTPARAMS
SUBJECTS

..

TESTFEEDPARAMS has a composite key namely:- testId, source, publisher.
TESTPARAMS has primary key testid
SUBJECTS primarykey is subjectName

so if we want to change the timeout for all the values of testIds in ASD and CSD and where roeflag is 'yes', what do you think, is the following query good or would you advise to do it a different way?

UPDATE TESTFEEDPARAMS
SET TIMEOUT =200
WHERE TESTID = (SELECT a.TESTID
FROM TESTPARAMS A, SUBJECTS B
WHERE A.TESTSUBJECT IN ('ASD', 'CSD')
AND A.TESTSUBJECT = B.SUBJECTNAME
AND B.ROEFLAG='YES')
Re: SQL query cleanup [message #228743 is a reply to message #228707] Wed, 04 April 2007 01:01 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Perhaps you should consider changing

"WHERE TESTID = (SELECT a.TESTID ..."

with

"WHERE TESTID IN (SELECT a.TESTID ..."

in order to avoid possible TOO-MANY-ROWS error.
Re: SQL query cleanup [message #228764 is a reply to message #228707] Wed, 04 April 2007 02:29 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
kilyas wrote on Tue, 03 April 2007 18:43

WHERE TESTID = (SELECT a.TESTID
FROM TESTPARAMS A, SUBJECTS B
WHERE A.TESTSUBJECT IN ('ASD', 'CSD')
AND A.TESTSUBJECT = B.SUBJECTNAME
AND B.ROEFLAG='YES')[/b]


change
where A.TESTSUBJECT IN ('ASD', 'CSD')
AND A.TESTSUBJECT = B.SUBJECTNAME
AND B.ROEFLAG='YES'

to

where
A.TESTSUBJECT = B.SUBJECTNAME
AND A.TESTSUBJECT IN ('ASD', 'CSD')
AND B.ROEFLAG='YES'

Re: SQL query cleanup [message #228791 is a reply to message #228764] Wed, 04 April 2007 03:33 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
muzahidul islam wrote on Wed, 04 April 2007 09:29

change
where A.TESTSUBJECT IN ('ASD', 'CSD')
AND A.TESTSUBJECT = B.SUBJECTNAME
AND B.ROEFLAG='YES'

to

where
A.TESTSUBJECT = B.SUBJECTNAME
AND A.TESTSUBJECT IN ('ASD', 'CSD')
AND B.ROEFLAG='YES'


Why?? You only swapped two where clauses, right?
Previous Topic: how to get a column value from database table
Next Topic: What is problem
Goto Forum:
  


Current Time: Sun Dec 04 16:18:25 CST 2016

Total time taken to generate the page: 0.05684 seconds