CODEWITHSUNDEEP
sqlsql-server-2008selectsql-update
Andrey
Andrey

Reputation: 1759

Update statement based on select

I have statement 

update Clients
set StatusID= 4
from (select c.clientid
from Clients c
where CategCode = 'CH' and StatusID in (1,2,6) and DATEDIFF(YEAR,dob,GETDATE())>5)

it throwing me error 

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ')'.

do you see what can cause this error?

I was trying to use this statement but it set status 4 for all clients 

update Clients
set StatusID= 4
WHERE EXISTS( 
select clientid,DOB,DATEDIFF(YEAR,dob,GETDATE())
from Clients 
where CategCode = 'CH' and StatusID in (1,2,6) and DATEDIFF(YEAR,dob,GETDATE())>5

Upvotes: 0

Views: 83

Answers (1)

rs.
rs.

Reputation: 27427

Try this

update c
set c.StatusID= 4
from Clients c
where CategCode = 'CH' and StatusID in (1,2,6) 
and DATEDIFF(YEAR,dob,GETDATE())>5

Your 2nd query should be

update c
set StatusID= 4
from Clients c
WHERE EXISTS( 
select 1
from Clients x
where CategCode = 'CH' and StatusID in (1,2,6)  and x.clientid = c.clientid)

Upvotes: 1

Related Questions