SQL Update query for select query

Question

i have the following query to list the employees of two table.

i need to update the a.staffdiscountstartdate to '20100428' how to rewrite the following query for this?

select 
    a.employeeid,
    b.employeeid 
from 
    tblEmployees a
        left join
    tblCards b 
        on
            a.employeeid=b.employeeid 
where 
    GroupStartDate < '20100301' 
and 
    StaffDiscountStartDate > '20100428' 
and 
    datediff(day,groupstartdate,staffdiscountstartdate)>1 
and
    b.employeeid is null

Answer 1

Should be just able to do:

UPDATE a
SET a.staffdiscountstartdate = '20100428'
from tblEmployees a
    left join tblCards b on a.employeeid=b.employeeid 
where GroupStartDate < '20100301' 
and StaffDiscountStartDate > '20100428' 
and datediff(day,groupstartdate,staffdiscountstartdate)>1 
and b.employeeid is null

MS SQL only. Other SQL versions don't support this syntax.

Answer 2

Two methods.

One:

update tblEmployees
set staffdiscountstartdate = '20100428' 
where employeeid in (
    -- original select query here, remove b.employeeid from the select results
)

Two:

update a
set a.staffdiscountstartdate = '20100428' 
from tblEmployees a
    left join
tblCards b 
    on
        a.employeeid=b.employeeid 
where 
    GroupStartDate < '20100301' 
and 
    StaffDiscountStartDate > '20100428' 
and 
    datediff(day,groupstartdate,staffdiscountstartdate)>1 
and
    b.employeeid is null

Either will work.

Answer 3

update
    tblEmployees
set
    staffdiscountstartdate = '20100428'
where
    employeeid in (
    select 
        a.employeeid
    from 
        tblEmployees a
            left join
        tblCards b 
            on
                a.employeeid=b.employeeid 
    where 
        GroupStartDate < '20100301' 
    and 
        StaffDiscountStartDate > '20100428' 
    and 
        datediff(day,groupstartdate,staffdiscountstartdate)>1 
    and
        b.employeeid is null
    )

Answer 4

Update a
Set  staffdiscountstartdate = '20100428' 
--select  a.employeeid,  b.employeeid  
from  
    tblEmployees a 
        left join 
    tblCards b  
        on 
            a.employeeid=b.employeeid  
where  
    GroupStartDate < '20100301'  
and  
    StaffDiscountStartDate > '20100428'  
and  
    datediff(day,groupstartdate,staffdiscountstartdate)>1  
and 
    b.employeeid is null 
and 
    a. staffdiscountstartdate <> '20100428'    

I added an additional where clause as who needs to update the value if it is already there correctly. I also showed how to use the select as part of the update by commenting out the select and column list part of the statement on one line. THis helps you see that you have the correct records before you run the update and I think it makes it easier to see how to convert a select statment to an update.