November 11, 2008 at 3:09 pm
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
November 11, 2008 at 6:41 pm
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]
November 12, 2008 at 2:58 am
Maybe:
DECLARE @TempTbl TABLE
(
    Col1 char(7)
    ,Col2 char(7)
    ,Col3 char(7)
    ,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