EXISTS in UPDATE statement [message #416934] |
Wed, 05 August 2009 04:31  |
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 #416936 is a reply to message #416934] |
Wed, 05 August 2009 04:41   |
 |
vamsi kasina
Messages: 2112 Registered: October 2003 Location: Cincinnati, OH
|
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
|
|
|
|