Distinct Update - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Distinct Update

Author

Topic

Looper
Yak Posting Veteran

68 Posts

Posted - 2009-02-17 : 05:59:34

Is it possible to do a Distinct Update.

For example, I have a select statement that selects all the distinct employees from an employment table. Employees can have multiple employments. This works fine.

But now I want to update a field in the employee table based on criteria from the employments table, but I want the script to be as efficient as possible, therefore I don't want it updating the same field multiple times for one employee if an employee has multiple employments.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-17 : 06:06:01

Post sample data and output, Its tough understanding the scenario otherwise.

Looper
Yak Posting Veteran

68 Posts

Posted - 2009-02-17 : 06:21:31

Below is what I currently have:
UPDATE ee
SET ee.Current = Null
FROM Employee ee
INNER JOIN Employment emp ON ee.eeId = emp.eeId
WHERE ISNULL(ee.Current,'') NOT IN ('Y') AND emp.State = '0'

If I do this as a select I assume I would do the following using distinct to get unique rows:
SELECT DISTINCT ee.eeid from Employee ee
INNER JOIN Employment emp ON ee.eeId = emp.eeId
WHERE ISNULL(ee.Current,'') NOT IN ('Y')
AND emp.State = '0'
order by esw.eeid

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-17 : 06:53:47

It won't update it multiple times,
Alternatively you could use this for update,


update Employee set [Current] = Null
where exists (select top 1 * from Employment where eeId=Employee.eeId   and State = '0' )
and ISNULL([Current],'') <>'Y'

Looper
Yak Posting Veteran

68 Posts

Posted - 2009-02-17 : 06:58:39

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-17 : 09:01:08

quote:
Originally posted by Looper

Below is what I currently have:
UPDATE ee
SET ee.Current = Null
FROM Employee ee
INNER JOIN Employment emp ON ee.eeId = emp.eeId
WHERE ISNULL(ee.Current,'') NOT IN ('Y') AND emp.State = '0'

If I do this as a select I assume I would do the following using distinct to get unique rows:
SELECT DISTINCT ee.eeid from Employee ee
INNER JOIN Employment emp ON ee.eeId = emp.eeId
WHERE ISNULL(ee.Current,'') NOT IN ('Y')
AND emp.State = '0'
order by esw.eeid

are you trying to set Current as Null for those employees with atleast one employement record with State = '0' or all employement records with stae = 0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources