Update based on query - 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)

Update based on query

Author

Topic

jhaney
Starting Member

33 Posts

Posted - 2011-02-23 : 10:33:04

I have the follwing query that returns the rows I need to update. can anyone tell me how to update based on the rows returned by a query.

(SELECT DISTINCT cl.CODE, cl.active
FROM CLREFER AS cl LEFT OUTER JOIN
MWAPPTS AS mw ON cl.COMPANY = mw.COMPANY AND cl.CODE = mw.REFERRAL
where cl.company = 'company'
AND (cl.ACTIVE = 'y')

and not exists(select *
FROM CLREFER AS cl1 LEFT OUTER JOIN
MWAPPTS AS mw ON cl1.COMPANY = mw.COMPANY AND cl1.CODE = mw.REFERRAL
where (cl1.company = cl.company)
and mw.adate > '1/1/2010'
and (cl1.code = cl.code)))

Thanks

Joshua

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-02-23 : 10:41:02

We can't see what you want to update...

No, you're never too old to Yak'n'Roll if you're too young to die.

jhaney
Starting Member

33 Posts

Posted - 2011-02-23 : 13:11:15

Oops forgot that part

update clrefer
set clrefer.active = 'N'

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2011-02-23 : 14:57:48

quote:
Originally posted by jhaney

Oops forgot that part

update clrefer
set clrefer.active = 'N'

And how exactly does this relate to your SELECT?

jhaney
Starting Member

33 Posts

Posted - 2011-02-23 : 15:01:13

I needed to update the rows being returned in the select statement. Just figiured it out though I used this

update clrefer
set active = 'N'
where clrefer.company = 'comapny'
and clrefer.code in (SELECT DISTINCT cl.CODE
FROM CLREFER AS cl LEFT OUTER JOIN
MWAPPTS AS mw ON cl.COMPANY = mw.COMPANY AND cl.CODE = mw.REFERRAL
where cl.company = 'company'
AND (cl.ACTIVE = 'y')

and not exists(select *
FROM CLREFER AS cl1 LEFT OUTER JOIN
MWAPPTS AS mw ON cl1.COMPANY = mw.COMPANY AND cl1.CODE = mw.REFERRAL
where (cl1.company = cl.company)
and mw.adate > '1/1/2010'
and (cl1.code = cl.code)))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources