Complex Case Statement in an Update

  • I am having an issue with the case statement and updates.

    I have a DB table and a table variable. They are both the same except that the table variable has only 1 row for a given column (called CASE_COL in my example below) and the DB Table has several rows for it.

    I need to find a way to update the table variable with out having to do a row by row select. I had thought my example below would do it, because when I run the update as a select it gives me multiple rows. I had hoped that the Case Statement could pull the correct row out and store it off. But it is not working.

    I would LOVE any help or ideas.

    declare @TempTbl TABLE (COL1 char(7), COL2 char(7), COL3 varchar(3), CASE_COL bigint)

    -- tblActualDBTable is identical to @TmpTbl except that in @TmpTbl I only want one instance of CASE_COL.

    -- There are many rows that differ only by CASE_COL in tblActualDBTable

    insert into @TempTbl(COL1, COL2, COL3, CASE_COL)

    select res2.COL1, res2.COL2, res2.COL3, -1

    from tblActualDBTable res2 (NoLock)

    group by res2.COL1, res2.COL2, res2.COL3

    having count(res2.CASE_COL) > 1

    update @TempTbl

    set CASE_COL =

    case

    -- If it is currently set then leave it there.

    when conflict.CASE_COL = 1 then 1

    -- If 1 is an option then chose it.

    when res.CASE_COL = 1 then 1

    -- If we don't have a 1 option yet (or are not going to get one) then make sure we keep a 2.

    when conflict.CASE_COL = 2 then 2

    -- If we have not yet selected 1 or 2 then take the 2 if it is here.

    when res.CASE_COL = 2 then 2

    -- If we get to this point then we will just take what ever CASE_COL is available.

    else res.CASE_COL

    end

    from tblActualDBTable res (NoLock)

    join @TempTbl conflict

    on res.COL1 = conflict.COL1

    and res.COL2 = conflict.COL2

    and res.COL3 = conflict.COL3

  • It is not at all clear what you are trying to do. In particular, this:

    They are both the same except that the table variable has only 1 row for a given column

    makes no sense. With tables, individual "rows" are not associated with individual "columns".

    Please give us some examples of you starting data and what you want to end up with.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Maybe:

    DECLARE @TempTbl TABLE

    (

    &nbsp&nbsp&nbsp&nbspCol1 char(7)

    &nbsp&nbsp&nbsp&nbsp,Col2 char(7)

    &nbsp&nbsp&nbsp&nbsp,Col3 char(7)

    &nbsp&nbsp&nbsp&nbsp,Case_Col bigint

    )

    INSERT INTO @TempTbl

    SELECT Col1, Col2, Col3, MIN(Case_Col)

    FROM tbleActualDBTable

    WHERE Case_Col > 0

    GROUP BY Col1, Col2, Col3

    HAVING COUNT(Case_Col) > 1

    otherwise post some sample data with expected results.

Viewing 3 posts - 1 through 2 (of 2 total)

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