update command

  • varshini

    Right there with Babe

    Points: 725

    Table : Employee

    EmployeeId Name MemoStatus

    1 a N

    2 b N

    3 c Y

    4 d N

    5 e y

    6 f N

    Table MemoDetail :

    EmpoyeeID iSSuedBy Date

    3 Edwin 2009-01-05

    3 Elsa 2009-02-10

    5 Rocky 2009-03-03

    5 bela 2009-02-01

    i have to update Employee Memostatus to 'N' based on records not exist on Memodetail table.

    Suppose employee has at least one memo record in memeoDetail table we should not update 'N' , it should be remain 'Y'

    how to achive this using a update command ?

  • arun.sas

    SSChampion

    Points: 11831

    Hi,

    try this

    update a

    set a.MemoStatus = (case when (select 1 from MemoDetail b where b.EmployeeId = a.EmployeeId )=1 then 'Y'else 'N' end)

    from Employee a

  • Paul White

    SSC Guru

    Points: 150341

    arun.sas (11/7/2009)


    Hi,

    try this

    update a

    set a.MemoStatus = (case when (select 1 from MemoDetail b where b.EmployeeId = a.EmployeeId )=1 then 'Y'else 'N' end)

    from Employee a

    declare @Employee table (employee_id int primary key, name char(1) not null, memostatus char(1) null);

    insert @Employee (employee_id, name) values (1, 'a');

    insert @Employee (employee_id, name) values (2, 'b');

    insert @Employee (employee_id, name) values (3, 'c');

    insert @Employee (employee_id, name) values (4, 'd');

    insert @Employee (employee_id, name) values (5, 'e');

    insert @Employee (employee_id, name) values (6, 'f');

    declare @MemoDetail table (employee_id int not null, issued_by varchar(10) not null, date datetime not null);

    insert @MemoDetail (employee_id, issued_by, date) values (3, 'Edwin', '2009-01-05');

    insert @MemoDetail (employee_id, issued_by, date) values (3, 'Elsa', '2009-02-10');

    insert @MemoDetail (employee_id, issued_by, date) values (5, 'Rocky', '2009-03-03');

    insert @MemoDetail (employee_id, issued_by, date) values (5, 'Bela', '2009-02-01');

    update a

    set a.MemoStatus = (case when (select 1 from @MemoDetail b where b.employee_id = a.employee_id )=1 then 'Y'else 'N' end)

    from @Employee a

    select * from @Employee

    [font="Courier New"]Msg 512, Level 16, State 1, Line 17

    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    [/font][font="Courier New"]The statement has been terminated.[/font]

  • Paul White

    SSC Guru

    Points: 150341

    declare @Employee table (employee_id int primary key, name char(1) not null, memostatus char(1) null);

    insert @Employee (employee_id, name) values (1, 'a');

    insert @Employee (employee_id, name) values (2, 'b');

    insert @Employee (employee_id, name) values (3, 'c');

    insert @Employee (employee_id, name) values (4, 'd');

    insert @Employee (employee_id, name) values (5, 'e');

    insert @Employee (employee_id, name) values (6, 'f');

    declare @MemoDetail table (employee_id int not null, issued_by varchar(10) not null, date datetime not null);

    insert @MemoDetail (employee_id, issued_by, date) values (3, 'Edwin', '2009-01-05');

    insert @MemoDetail (employee_id, issued_by, date) values (3, 'Elsa', '2009-02-10');

    insert @MemoDetail (employee_id, issued_by, date) values (5, 'Rocky', '2009-03-03');

    insert @MemoDetail (employee_id, issued_by, date) values (5, 'Bela', '2009-02-01');

    update E

    set memostatus =

    case

    when exists (select * from @MemoDetail M where M.employee_id = E.employee_id)

    then 'Y'

    else 'N'

    end

    from @Employee E;

    select * from @Employee

  • arun.sas

    SSChampion

    Points: 11831

    Paul White (11/7/2009[font="Courier New"]Msg 512, Level 16, State 1, Line 17

    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    [/font][font="Courier New"]The statement has been terminated.[/font]

    Thanks Paul,

    It should be like

    update a

    set a.MemoStatus = (case when (select distinct 1 from @MemoDetail b where b.employee_id = a.employee_id )=1 then 'Y'else 'N' end)

    from @Employee a

  • Paul White

    SSC Guru

    Points: 150341

    I prefer the EXISTS. The plan seems more efficient, and the construct more natural.

    TOP (1) would also work in place of the DISTINCT, but I'd still prefer the semi join.

  • Florian Reischl

    SSC-Dedicated

    Points: 37299

    Hi Guys!

    @Paul: Thanks for test data! 🙂

    I always prefer the joined UPDATE:

    UPDATE e SET

    e.memostatus = CASE WHEN m.employee_id IS NULL THEN 'N' ELSE 'Y' END

    FROM @Employee e

    LEFT JOIN @MemoDetail m ON e.employee_id = m.employee_id

    If there are many duplicates in memo-details table, I'd use a CTE:

    ; WITH m (employee_id) AS

    (

    SELECT DISTINCT

    employee_id

    FROM @MemoDetail

    )

    UPDATE e SET

    e.memostatus = CASE WHEN m.employee_id IS NULL THEN 'N' ELSE 'Y' END

    FROM @Employee e

    LEFT JOIN m ON e.employee_id = m.employee_id

    Greets

    Flo

  • Paul White

    SSC Guru

    Points: 150341

    Florian Reischl (11/7/2009)


    @Paul: Thanks for test data! 🙂

    You're very welcome. I'm hoping that if I do it enough, future posters will get the hint 🙂

    I know not everything in life is about performance, but if you compare the query plans for your preferred methods to the EXISTS version, I hope you will see why I prefer it.

    The EXISTS does not need a Stream Aggregate (present in both of your examples) and uses a left semi join instead of a left outer join. The obvious difference there is that the semi join stops processing as soon as it finds a match; the left outer join processes all matches.

    Query plans:

    1. EXISTS

    2. LEFT JOIN

    3. CTE

    Paul

  • Florian Reischl

    SSC-Dedicated

    Points: 37299

    Paul White (11/7/2009)


    The EXISTS does not need a Stream Aggregate (present in both of your examples) and uses a left semi join instead of a left outer join. The obvious difference there is that the semi join stops processing as soon as it finds a match; the left outer join processes all matches.

    Thanks for your explanation!

    I know, the joined UPDATE is slightly less performing in this case. The main reason why I'm using it is, I've seen several inline sub-queries which ended up in a row based execution. Since I don't know the SQL Server internals like you do, the joined syntax is more secure for newbies like me :-P.

    Greets

    Flo

  • Paul White

    SSC Guru

    Points: 150341

    Newbies??? Flo - in the words of a very popular drink over here..."yeah, right!"

    http://www.tui.co.nz/

  • Florian Reischl

    SSC-Dedicated

    Points: 37299

    Well, probably I'm no newbie :cool:. But there are still situations I completely don't understand SQL Server's intentions... (Just had a really strange behavior last week which appeared completely stupid to me.)

    As long as I don't understand those things I prefer the secure in some situations.

    Greets

    Flo

  • Paul White

    SSC Guru

    Points: 150341

    Florian Reischl (11/7/2009)


    Just had a really strange behavior last week which appeared completely stupid to me.

    Feel free to share! PM or The Thread...

    Florian Reischl (11/7/2009)


    As long as I don't understand those things I prefer the secure familiar in some situations.

    Fixed that for you 🙂

  • Florian Reischl

    SSC-Dedicated

    Points: 37299

    Paul White (11/7/2009)


    Florian Reischl (11/7/2009)


    Just had a really strange behavior last week which appeared completely stupid to me.

    Feel free to share! PM or The Thread...

    Good idea. I'll try to setup a test environment and start a thread.

    Thanks

    Flo

  • Florian Reischl

    SSC-Dedicated

    Points: 37299

    Paul White (11/7/2009)


    Florian Reischl (11/7/2009)


    Just had a really strange behavior last week which appeared completely stupid to me.

    Feel free to share! PM or The Thread...

    Feel free to join:

    http://www.sqlservercentral.com/Forums/Topic815391-338-1.aspx

    🙂

  • Paul White

    SSC Guru

    Points: 150341

    Nice problem there Flo, and good to get it solved so quickly.

    I hope other posters imitate your set-up scripts!

    Paul

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply