CODEWITHSUNDEEP
sqlsql-servert-sql
SmartestVEGA
SmartestVEGA

Reputation: 8889

SQL Update query for select query

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

Upvotes: 2

Views: 2173

Answers (4)

HLGEM
HLGEM

Reputation: 96552

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.

Upvotes: 0

Mario Menger
Mario Menger

Reputation: 5902

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
    )

Upvotes: 1

technophile
technophile

Reputation: 3676

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.

Upvotes: 2

BradC
BradC

Reputation: 39916

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.

Upvotes: 2

Related Questions