update multiple column based on case statement??

vinod_yadav1919 · July 3rd, 2008, 03:21 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

Cheers :)

vinod

Old Pedant · July 3rd, 2008, 04:54 PM

*** CROSSPOST ***

Answered in Database / SQL Language forum.

Don't these Wrox forums discourage cross-posting???? If not, they should.

joefawcett · July 3rd, 2008, 04:58 PM

Are you using SQL Server 2005? If so you can first turn the value table into something easier to manage using the PIVOT operator. (If using an earlier version then you can still create the cross tab table but it's messier.)

Code:

SELECT EmpId, [10] FirstName, [20] LastName, [30] Address
FROM
(SELECT EmpId, NewValue, Id FROM ValueTable) VT
PIVOT
(
  MAX(NewValue) FOR Id IN ([10], [20], [30])
) PVT

This enables the UPDATE to be written as:

Code:

UPDATE EmployeeTable
  SET 
    FirstName = CASE ET.FirstName WHEN 'N' THEN UT.FirstName ELSE ET.FirstName END,
    LastName =  CASE ET.LastName WHEN 'N' THEN UT.LastName ELSE ET.LastName END,
    Address = CASE ET.Address WHEN 'N' THEN UT.Address ELSE ET.Address END
  FROM
    EmployeeTable ET INNER JOIN
    (SELECT EmpId, [10] FirstName, [20] LastName, [30] Address
FROM
(SELECT EmpId, NewValue, Id FROM ValueTable) VT
PIVOT
(
  MAX(NewValue) FOR Id IN ([10], [20], [30])
) PVT) UT
ON ET.EmpId = UT.EmpId;

--

Joe (Microsoft MVP - XML)

joefawcett · July 3rd, 2008, 05:22 PM

Quote:

quote:Originally posted by Old Pedant
*** CROSSPOST ***

Answered in Database / SQL Language forum.

Don't these Wrox forums discourage cross-posting???? If not, they should.

They certainly do but remember the mantra, rules only apply to others :)

--

Joe (Microsoft MVP - XML)

vinod_yadav1919 · July 3rd, 2008, 06:18 PM

Hi Joe,
Thank you for providing the inputs, I am using SQL2000.
My sincere aplogoies for cross post.
If you have any example for doing this in SQL2000 it would be a great help!!

Cheers :)

vinod

vinod_yadav1919 · July 3rd, 2008, 06:45 PM

Hi All,
Now I am able to update in SQL2000..
Thanks again all of you to give the pointer on it

Cheers :)

vinod

Old Pedant · July 3rd, 2008, 07:29 PM

So what did you end up using???

You should post your working answer so others who might stumble upon this will not have to ask again.