Why is this not working (update with CASE)

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

Transact-SQL (2000)

Why is this not working (update with CASE)

Author

Topic

MuadDBA

628 Posts

Posted - 2008-03-20 : 15:15:05

I have 2 tables, report and info (thisis radically simplified, but makes it easier) and I want to update each row in report with a different piece of information from the info table without doing multiple passes through the info table. This is what I am attempting, and I don't understand why it is not working.



create table report (
id tinyint,
person varchar(10) null,
addr varchar(25) null,
ssn varchar(11) null)

go

create table info (
id tinyint,
info_type varchar(10),
info varchar(25))

go

insert into report(id) values (1)
insert into report(id) values (2)
insert into report(id) values (3)
insert into info values (1,'person','Joe')
insert into info values (1,'addr','1234 Joe Place')
insert into info values (1,'ssn','123-45-6789')
insert into info values (2,'person','Bob')
insert into info values (2,'addr','1234 Bob Place')
insert into info values (2,'ssn','34-567-8910')
insert into info values (3,'person','Jim')
insert into info values (3,'addr','1234 Jim Place')
insert into info values (3,'ssn','89-123-4567')

update report
set person = CASE
	when info_type = 'person' then info else person end,
addr = CASE
	when info_type = 'addr' then info else addr end,
ssn = case 
	when info_type = 'ssn' then info else ssn end
from report left join info on 
info.id = report.id

What I would like to appear is this:


select * from report
id  person  addr            ssn
1   Joe     1234 Joe Place  12-345-6789
2   Bob     1234 Bob Place  34-567-8910
3   Jim     1234 Jim Place  89-123-4567

but what I get is this


id  person  addr            ssn
1   NULL    NULL            12-345-6789
2   NULL    NULL            34-567-8910
3   NULL    NULL            89-123-4567

Help?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-20 : 15:22:41

That will update the same row multiple times with different values, so the final result is undefined.

- Jeff
http://weblogs.sqlteam.com/JeffS

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-03-20 : 15:28:33

Try this

update report
set person = (select info from info where report.id = id and info_type = 'person'),
addr = (select info from info where report.id = id and info_type = 'addr'),
ssn = (select info from info where report.id = id and info_type = 'ssn')

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-03-20 : 15:41:15

or this:


set nocount on
set ansi_warnings off

declare @report table (
id tinyint,
person varchar(10) null,
addr varchar(25) null,
ssn varchar(11) null)


declare @info table (
id tinyint,
info_type varchar(10),
info varchar(25))

insert into @report(id) values (1)
insert into @report(id) values (2)
insert into @report(id) values (3)
insert into @info values (1,'person','Joe')
insert into @info values (1,'addr','1234 Joe Place')
insert into @info values (1,'ssn','123-45-6789')
insert into @info values (2,'person','Bob')
insert into @info values (2,'addr','1234 Bob Place')
insert into @info values (2,'ssn','34-567-8910')
insert into @info values (3,'person','Jim')
insert into @info values (3,'addr','1234 Jim Place')
insert into @info values (3,'ssn','89-123-4567')

update r set
       r.person = d.person
       ,r.addr = d.addr
       ,r.ssn = d.ssn
from   (
       select r.[id]
              ,person = max(case when i.info_type = 'person' then i.info end)
              ,addr = max(case when i.info_type = 'addr' then info end)
              ,ssn = max(case when i.info_type = 'ssn' then info end)
       from   @report r
       join   @info i on i.[id] = r.[id]
       group by r.[id]
       ) d
join   @report r 
              on r.[id] = d.[id]

select * from @report

output:
id   person     addr                      ssn
---- ---------- ------------------------- -----------
1    Joe        1234 Joe Place            123-45-6789
2    Bob        1234 Bob Place            34-567-8910
3    Jim        1234 Jim Place            89-123-4567

Be One with the Optimizer
TG

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-20 : 15:57:09

Of course, with TG's solution, don't update at all. Just INSERT INTO the table with the values from the SELECT:

insert into @r (id, person,addr,ssn)
select i.[id]
,person = max(case when i.info_type = 'person' then i.info end)
,addr = max(case when i.info_type = 'addr' then info end)
,ssn = max(case when i.info_type = 'ssn' then info end)
from @info i
group by i.[id]

- Jeff
http://weblogs.sqlteam.com/JeffS

bfoster
Starting Member

30 Posts

Posted - 2008-03-20 : 17:33:31

Here's a slightly different option. Depending on what the data will look like you may need left joins and use coalesce as in the second statement.

UPDATE report
SET person = p.info,
addr = a.info,
ssn = s.info
FROM report r
INNER JOIN info p ON r.id = p.id AND p.info_type = 'person'
INNER JOIN info a ON r.id = a.id AND a.info_type = 'addr'
INNER JOIN info s ON r.id = s.id AND s.info_type = 'ssn'

UPDATE report
SET person = COALESCE(p.info, person),
addr = COALESCE(a.info, addr),
ssn = COALESCE(s.info, ssn)
FROM report r
LEFT JOIN info p ON r.id = p.id AND p.info_type = 'person'
LEFT JOIN info a ON r.id = a.id AND a.info_type = 'addr'
LEFT JOIN info s ON r.id = s.id AND s.info_type = 'ssn'

MuadDBA

628 Posts

Posted - 2008-03-24 : 13:37:52

Thanks to all. I used TG's solution because I needed the update (my table already had the data) and I wanted to reduce the # of scans on the info table (using multiple joins results in multiple scans).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources