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 2005 Forums
 Transact-SQL (2005)
 Multi Column Update Problem

Author  Topic 

y2n
Starting Member

5 Posts

Posted - 2011-04-07 : 18:13:20
I am trying to update a table (T1) that has an ID column and then several columns that flatten out the data from a related table (T2) by putting the data into a different column based on a value in one of the fields in T2. Each column in T1 (A, B and C) can be overloaded by multiple values from t2 (one from each record that matches the code).

I can do this in MS Access using the SQL Server tables, but the stored proc in SQL is giving me strange results. Only one column ends up being updated and all the others are remaining, or being reset to, NULL.

I am truly boggled by this right now. I have tried several permutations of this, including using IF ... THEN syntax, and get the same type of behaviour.

I am inlculding all code necessary to create and test, so please help.


CREATE TABLE Table_1(ID int, A varchar(5),B varchar(5), C varchar(5))
INSERT INTO Table_1 VALUES (1, NULL, NULL, NULL)
INSERT INTO Table_1 VALUES (2, NULL, NULL, NULL)
INSERT INTO Table_1 VALUES (3, NULL, NULL, NULL)


CREATE TABLE Table_2(ID int, CODE varchar(5), EMAIL varchar(5))
INSERT INTO Table_2 VALUES (1, 'A','1A')
INSERT INTO Table_2 VALUES (1, 'B','1B')
INSERT INTO Table_2 VALUES (1, 'C','1C')
INSERT INTO Table_2 VALUES (2, 'A','2A')
INSERT INTO Table_2 VALUES (2, 'B','2B')
INSERT INTO Table_2 VALUES (2, 'B','2B2')
INSERT INTO Table_2 VALUES (3, 'A','3A')
INSERT INTO Table_2 VALUES (3, 'C','3C')
INSERT INTO Table_2 VALUES (3, 'C','3C2')

-- The Update that I am trying to run
UPDATE t1
SET
A = CASE
WHEN (t2.code LIKE 'A' AND t1.a IS NULL) THEN t2.email
WHEN (t2.code LIKE 'A' AND NOT t1.a IS NULL) THEN t1.a + ',' + t2.email
ELSE t1.a
END
,B = CASE
WHEN (t2.code LIKE 'B' AND t1.b IS NULL) THEN t2.email
WHEN (t2.code LIKE 'B' AND NOT t1.b IS NULL) THEN t1.b + ',' + t2.email
ELSE t1.b
END
,C = CASE
WHEN (t2.code LIKE 'C' AND t1.c IS NULL) THEN t2.email
WHEN (t2.code LIKE 'C' AND NOT t1.c IS NULL) THEN t1.c + ',' + t2.email
ELSE t1.c
END
--SELECT *
FROM
Table_1 t1
INNER JOIN Table_2 t2 ON t2.ID = t1.id

/*Expected Results in Table 1
1 1A 1B 1C
2 2A 2B,2B2 NULL
3 3A NULL 3C,3C2

Actual Results
1 1A NULL NUll
2 2A NULL NULL
3 3A NULL NULL
*/

/* -- Code that works in MS Access using the same SQL Server tables.
UPDATE Table_1 AS t1 INNER JOIN Table_2 AS t2 ON t1.ID = t2.ID SET t1.A = IIf([t2].[code] Like 'A',IIf(IsNull([t1].[a]),[t2].[email],[t1].[a] & ',' & [t2].[email]),[t1].[a]), t1.B = IIf([t2].[code] Like 'B',IIf(IsNull([t1].[b]),[t2].[email],[t1].[b] & ',' & [t2].[email]),[t1].[b]), t1.C = IIf([t2].[code] Like 'C',IIf(IsNull([t1].[c]),[t2].[email],[t1].[c] & ',' & [t2].[email]),[t1].[c]);
*/

y2n
Starting Member

5 Posts

Posted - 2011-04-08 : 13:01:22
No responses yet? I see that a few people have viewed this post, but nobody has replied. Do you need more info? I know there is a simple solution, as I am sure I have done this before, but I just can't see the forest for the trees right now.
Go to Top of Page

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2011-04-09 : 12:13:59
Hi,

if i understand correctly you need something like this:

select *
from table_2
pivot
(
max(email)
for code in ([A],[B],[C])
) as p
Go to Top of Page

y2n
Starting Member

5 Posts

Posted - 2011-04-11 : 12:02:26
That is close, but it only returns one email value (the max one). What I need to do is concatenate all the values from that field for each code.
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-11 : 14:54:08
Here is my adapation of slimt_slimt's code.

SELECT * FROM
(
SELECT distinct
a.Id,
a.Code,
b.EmailList
FROM
Table_2 a
CROSS APPLY
(
SELECT STUFF((
SELECT ',' + EMAIL AS [text()]
FROM Table_2 b
WHERE a.Id = b.Id AND a.Code = b.CODE
FOR XML PATH(''), TYPE
).value('.','varchar(max)'),1,1,'') AS EmailList
) b
)c
PIVOT
(MAX(EmailList) FOR Code IN ([A],[B],[C])) P
Go to Top of Page

y2n
Starting Member

5 Posts

Posted - 2011-04-11 : 20:05:14
@sunitabeck and @slimt, thanks for for the help. I tweaked the code to actually update Table_1 with those values. Here is the final result:


UPDATE Table_1
SET
a = p.a,
b = p.b,
c = p.c

--SELECT *
FROM
(
SELECT distinct
a.Id,
a.Code,
b.EmailList
FROM
Table_2 a
CROSS APPLY
(SELECT STUFF
(
(
SELECT ',' + EMAIL AS [text()]
FROM Table_2 b
WHERE a.Id = b.Id AND a.Code = b.CODE
FOR XML PATH(''), TYPE
).value('.','varchar(max)'),1,1,''
) AS EmailList
) b
) c
PIVOT
(MAX(EmailList) FOR Code IN ([A],[B],[C])) P
JOIN Table_1 ON p.id = Table_1.id




Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-11 : 20:20:53
I had forgotten that you were trying to update the table rather than just select the results. But with your changes it looks perfect!!
Go to Top of Page
   

- Advertisement -