UPDATE From Another Table With CASE Statement
-
I would like to UPDATE a table (table_1) with values from another table (table_2). Here is the logic: table_2 has a column named Code which can contain one of three values, either 1, 2, or 3. Depending on the value of Code, I would like to implement the following UPDATE statement (I'm sure it is wrong, but I would like something like the following pseudocode):
UPDATE table_1
CASE WHEN Code = 1 THEN
SET StatusID = 3,
StatusDT = t2.BoxDT
FROM table_1 AS t1 INNER JOIN
table_2 AS t2 ON t1.BoxNum = t2.BoxNum
WHERE t1.StatusID <> 3
CASE WHEN Code = 2 THEN
SET StatusID = 8,
StatusDT = t2.BoxDT
FROM table_1 AS t1 INNER JOIN
table_2 AS t2 ON t1.BoxNum = t2.BoxNum
WHERE t1.StatusID <> 8
CASE WHEN Code = 3 THEN
SET StatusID = 13,
StatusDT = t2.BoxDT
FROM table_1 AS t1 INNER JOIN
table_2 AS t2 ON t1.BoxNum = t2.BoxNum
WHERE t1.StatusID <> 13
END
Here is the table structures for the two tables:
table_1 table_2
table_1_ID PK table_2_ID PK
BoxNum BoxNum
StatusID FK Code
Employee BoxDT
StatusDT Flag
Hopefully someone has done this in the past or can assist me with their expertise. Thanks in advance for any help.
-
How about something like....
UPDATE T1
SET T1.StatusID =
CASE WHEN T2.Code = 1 THEN 3
WHEN T2.Code = 2 THEN 8
WHEN T2.Code = 3 THEN 13
END
FROM table_1 T1
INNER JOIN table_2 T2
ON T1.BoxNum = T2.BoxNum
-- Next part Optional to not update with same value
AND T1.StatusID <>
CASE WHEN T2.Code = 1 THEN 3
WHEN T2.Code = 2 THEN 8
WHEN T2.Code = 3 THEN 13
END
Once you understand the BITs, all the pieces come together
-
I would create a @variableTable and then populate it using the Case statement. Then update your table against the @variableTable.
Here is an example:
This example will screw up a couple of "address"(es) in your pubs.author table, but its harmless.
declare @myVarTable table (au_id varchar(16), lname varchar(32), fname varchar(32), myResult varchar(32))
INSERT INTO @myVarTable
SELECT
au_id,
au_lname ,
au_fname ,
CASE (state)
WHEN 'KS' THEN 'thisIsKS'
WHEN 'MD' THEN 'thisIsMD'
ELSE address
END as resultOfMyCaseStatement
--select *
FROM authors
select * from @myVarTable
UPDATE authors
SET address = (select myResult
FROM @myVarTable myt
WHERE authors.au_id = myt.au_id)
FROM authors, @myVarTable
select address from authors
-
You also need to look closely how you're using your CASE statement.
its useage/mentality is not exactly the same as VB or other languages.
CASE (state)
WHEN 'KS' THEN 'thisIsKS'
WHEN 'MD' THEN 'thisIsMD'
ELSE address
END as resultOfMyCaseStatement
Notice your checking the VALUE of a field.
And then your WHEN are based on the value of that field.
And the result of the case statement is a New Column (resultOfMyCaseStatement)
You had:
CASE WHEN T2.Code = 1 THEN 3
WHEN T2.Code = 2 THEN 8
WHEN T2.Code = 3 THEN 13
END
This is wrong I think.
Case (T2.Code)
When 2 Then 8
When 3 Then 13
Else 0 (or you can put a table.column here , its a little flexible)
End AS MyResultingColumn
Notice you get a column back. This is why I like the @variabletable solution.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply