Help me write this Update - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Help me write this Update

Author

Topic

sehnsucht
Starting Member

8 Posts

Posted - 2009-07-11 : 12:38:38

I'd like to write an Update statement which updates several columns in one go. This is trivial in some circumstances, but for my needs I've been forced to issue a seperate statement for each column, and I was wondering if someone could explain what I'm doing wrong and how to fix it, assuming there is a solution. I'm clearly missing something about how multiple columns in multiple rows are updated.

You can cut and paste the rest of this post straight into your UI. I'm actually using SQL Server 2008 to test this, but the solution has to work on 2000 and I'm getting identical results.

Cheers,
Alex.

/*
Here's the table I want to Update:
this table is returned to the client.
either col1 and col2 may be populated, or both, or neither
here's an example of what I'd like to see, given the test data I'm using

emp col1 col2
--- ---- ----
1 NULL NULL
2 21 NULL
3 NULL 32
4 41 NULL

*/

declare @report table(
emp int,
col1 int,
col2 int
)

/*
the data I want to populate @report with comes from here.
I include test data

*/
declare @somedata table(
emp int,
id int,
value int )

insert into @somedata ( emp,id,value )
select 1,1,null
union all
select 1,2,null
union all
select 2,1,21
union all
select 2,2,null
union all
select 3,1,null
union all
select 3,2,32
union all
select 4,1,41
union all
select 4,2,42

/*
create a record for each emp (regardless of whether they have data)
note that this is a cut down example of a problem I'm having. I don't want to work around
this problem by populating @report via a select - it has to be via an Update
*/
insert into @report (emp)
select distinct emp from @somedata

--This is one of the many Update statements I've attempted:
update r
set r.col1 = case when s.id = 1 then s.value else r.col1 end,
r.col2 = case when s.id = 2 then s.value else r.col2 end
from @report r
join @somedata s
on r.emp = s.emp

/*
I'm getting

emp col1 col2
--- ---- ----
1 NULL NULL
2 21 NULL
3 32 NULL
4 41 NULL

I'd like col1 and col2 reversed!

*/

select * from @report r order by r.emp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-11 : 12:43:17

Tried this?

SELECT		emp,
		MAX(CASE WHEN id = 1 THEN value ELSE NULL END) AS Col1,
		MAX(CASE WHEN id = 2 THEN value ELSE NULL END) AS Col2
FROM		@somedata
GROUP BY	emp

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-11 : 12:48:32

[code]UPDATE r
SET r.Col1 = s.Col1,
r.Col2 = s.Col2
FROM @Report AS r
INNER JOIN (
SELECT emp,
MAX(CASE WHEN id = 1 THEN value ELSE NULL END) AS Col1,
MAX(CASE WHEN id = 2 THEN value ELSE NULL END) AS Col2
FROM @somedata
GROUP BY emp
) AS s ON s.emp = r.emp[/code]

N 56°04'39.26"
E 12°55'05.63"

sehnsucht
Starting Member

8 Posts

Posted - 2009-07-11 : 13:28:39

quote:

Originally posted by Peso

UPDATE		r
SET		r.Col1 = s.Col1,
		r.Col2 = s.Col2
FROM		@Report AS r
INNER JOIN	(
			SELECT		emp,
					MAX(CASE WHEN id = 1 THEN value ELSE NULL END) AS Col1,
					MAX(CASE WHEN id = 2 THEN value ELSE NULL END) AS Col2
			FROM		@somedata
			GROUP BY	emp
		) AS s ON s.emp = r.emp

N 56°04'39.26"
E 12°55'05.63"

That's it. I understand what you're doing, but it's not intuitive to me, the MAX part especially.

Incidentally, what's going wrong with my original attempt? How is data getting read from the 'wrong' location (s.id =2) ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources