Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old July 3rd, 2008, 03:21 PM
Friend of Wrox
 
Join Date: Oct 2004
Location: delhi, delhi, India.
Posts: 553
Thanks: 0
Thanked 1 Time in 1 Post
Send a message via MSN to vinod_yadav1919 Send a message via Yahoo to vinod_yadav1919
Default update multiple column based on case statement??

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
__________________
Cheers :)

vinod
Reply With Quote
  #2 (permalink)  
Old July 3rd, 2008, 04:54 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

*** CROSSPOST ***

Answered in Database / SQL Language forum.

Don't these Wrox forums discourage cross-posting???? If not, they should.
Reply With Quote
  #3 (permalink)  
Old July 3rd, 2008, 04:58 PM
joefawcett's Avatar
Wrox Author
Points: 9,763, Level: 42
Points: 9,763, Level: 42 Points: 9,763, Level: 42 Points: 9,763, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

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)
Reply With Quote
  #4 (permalink)  
Old July 3rd, 2008, 05:22 PM
joefawcett's Avatar
Wrox Author
Points: 9,763, Level: 42
Points: 9,763, Level: 42 Points: 9,763, Level: 42 Points: 9,763, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

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)
Reply With Quote
  #5 (permalink)  
Old July 3rd, 2008, 06:18 PM
Friend of Wrox
 
Join Date: Oct 2004
Location: delhi, delhi, India.
Posts: 553
Thanks: 0
Thanked 1 Time in 1 Post
Send a message via MSN to vinod_yadav1919 Send a message via Yahoo to vinod_yadav1919
Default

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
Reply With Quote
  #6 (permalink)  
Old July 3rd, 2008, 06:45 PM
Friend of Wrox
 
Join Date: Oct 2004
Location: delhi, delhi, India.
Posts: 553
Thanks: 0
Thanked 1 Time in 1 Post
Send a message via MSN to vinod_yadav1919 Send a message via Yahoo to vinod_yadav1919
Default

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

Cheers :)

vinod
Reply With Quote
  #7 (permalink)  
Old July 3rd, 2008, 07:29 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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.
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
update multiple columns in an update statement debbiecoates SQL Server 2000 1 August 17th, 2008 04:01 AM
multiple column update query with Case vinod_yadav1919 SQL Language 4 July 3rd, 2008 06:53 PM
case statement Hudson40 Access VBA 1 February 11th, 2005 11:31 AM
Using A CASE Statement fastcorvette Access 5 December 24th, 2003 01:39 PM
case statement jakeone Beginning PHP 10 August 19th, 2003 03:03 PM



All times are GMT -4. The time now is 05:59 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.