multiple column update query with Case

vinod_yadav1919 · #1 (**permalink**) July 3rd, 2008, 03:20 PM

Hii Alll
Is it possible to update multiple column based on case statement??
employeeTable
_________________
empID FirstName LastName Address
1 N N N
2 N N N
3 N N N

ValueTable
----------------------
empID NewVAlue ID
1 'FN' 10
1 'LN' 20
2 'AA' 10
2 'BB' 30
3 'AA1' 10
3 'BB1' 20
3 'cc1' 30

updated employeeTable output result should be
_________________
empID FirstName LastName Address
1 FN LN N
2 AA N BB
3 AA1 BB1 CC1

UPDATE employeeTable
SET firstname = CASE When(employeeTable.firstName='N' and ID=10) THEN ValueTable.NewValue Else employeeTable.firstName End,
lastname = CASE When(employeeTable.lastname='N' and ID=20) THEN ValueTable.NewValue Else employeeTable.lastname End,
address = CASE When(employeeTable.address ='N' and ID=30) THEN ValueTable.NewValue Else employeeTable.address End
FROM ValueTable
where employeeTable.Empid=ValueTable.Empid

Any pointer will be great help

Cheers :)

vinod

Old Pedant · #2 (**permalink**) July 3rd, 2008, 04:52 PM

I *believe* you simply need to do something like this:

Code:

UPDATE employeeTable
SET firstname =  CASE When(employeeTable.firstName='N' and ID=10) THEN ValueTable.NewValue   Else employeeTable.firstName End,
    lastname =  CASE When(employeeTable.lastname='N' and ID=20) THEN ValueTable.NewValue  Else employeeTable.lastname End,
    address =  CASE When(employeeTable.address ='N' and ID=30) THEN  ValueTable.NewValue Else employeeTable.address End
FROM  employeeTable, ValueTable
where employeeTable.Empid = ValueTable.Empid

That is, you just need to repeat the table name in the FROM clause.

vinod_yadav1919 · #3 (**permalink**) July 3rd, 2008, 05:07 PM

Hi Pedant!!
I tried it,it will not show correct result.

Cheers :)

vinod

Old Pedant · #4 (**permalink**) July 3rd, 2008, 06:39 PM

Try it with an alias???

An alias for second use of the main table?

UPDATE employeeTable
SET firstname = CASE When(ET.firstName='N' and ID=10) THEN VT.NewValue Else ET.firstName End,
lastname = CASE When(ET.lastname='N' and ID=20) THEN VT.NewValue Else ET.lastname End,
address = CASE When(ET.address ='N' and ID=30) THEN VT.NewValue Else ET.address End
FROM employeeTable ET, ValueTable VT
where ET.Empid = VT.Empid

This comes from the MS docs for UPDATE. But they don't try something quite as complex as this.

I'm sure we could easily do this in a Stored Proc via a #temp table, but that just offends my sensibilities <grin/>.

Old Pedant · #5 (**permalink**) July 3rd, 2008, 06:53 PM

Yeah, I just tried that and it worked.

Simplified to only name column in the employees table, but worked like a charm:

Code:

update dbo.emps
set name = (CASE WHEN E.name = 'N' AND V.id=10 
                      THEN V.val
                      ELSE E.name END)
from dbo.emps AS E, dbo.vals AS V
where E.empid = V.empid

Need to download code?

Navigation