Home » SQL & PL/SQL » SQL & PL/SQL » EXISTS in UPDATE statement (Oracle 9204, XP)
EXISTS in UPDATE statement [message #416934] Wed, 05 August 2009 04:31 Go to next message
saharookiedba
Messages: 56
Registered: September 2007
Location: PUNE
Member
Hi Experts,

I am trying to understand the usage of EXISTS function in ORACLE.

i have a Query

UPDATE TBLTSEMPLOYEES SET GROUPID = 'U5' 
WHERE EMPID IN (SELECT EMPID FROM TBLTSEMPLOYEES WHERE ISACTIVE='Y' AND GROUPID IS NULL)


this updates 190 rows

i want to use EXISTS in place of IN, i tried

UPDATE TBLTSEMPLOYEES SET GROUPID = 'U5' 
WHERE EXISTS (SELECT EMPID FROM TBLTSEMPLOYEES WHERE ISACTIVE='Y' AND GROUPID IS NULL)


this updates 12013 rows (entire table)
please let me know how to use exists in this scenario..
Re: EXISTS in UPDATE statement [message #416935 is a reply to message #416934] Wed, 05 August 2009 04:39 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Try
UPDATE TBLTSEMPLOYEES a SET GROUPID = 'U5' 
WHERE EXISTS (SELECT null FROM TBLTSEMPLOYEES b WHERE ISACTIVE='Y' AND GROUPID IS NULL and a.empid = b.empid)

[Updated on: Wed, 05 August 2009 04:42]

Report message to a moderator

Re: EXISTS in UPDATE statement [message #416936 is a reply to message #416934] Wed, 05 August 2009 04:41 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
In this scenario, you must use correlated join with exists.
UPDATE TBLTSEMPLOYEES te
SET GROUPID = 'U5' 
WHERE EXISTS (SELECT 1 -- No need to select only empid
              FROM TBLTSEMPLOYEES 
              WHERE ISACTIVE='Y' 
              AND GROUPID IS NULL
              AND EMPID = te.EMPID)
It just check whether any related row is present or not.

By
Vamsi

EDIT: pablolee... I was late. I didn't see your reply.

[Updated on: Wed, 05 August 2009 04:43]

Report message to a moderator

Re: EXISTS in UPDATE statement [message #416939 is a reply to message #416936] Wed, 05 August 2009 04:47 Go to previous message
saharookiedba
Messages: 56
Registered: September 2007
Location: PUNE
Member
Thank You Both of you
Previous Topic: chat
Next Topic: Risks or disadvantages of using database links
Goto Forum:
  


Current Time: Mon Dec 05 23:58:25 CST 2016

Total time taken to generate the page: 0.06663 seconds