update statement
-
hi,
simplified form-
i have a table (table1) which contains 4 fields (Gid,Pid,Value,Cat). In the 'cat' field there 3 possible values (a,b & c).
i have a second table (table2), which contains 5 fields (gid,pid,a,b,c)
As you can see, the values in table1, 'cat' match the fieldnames in table2 (a,b & c)
My goal is to have an update statement which takes the data from table1 and updates it into table2 and this is what i have so far..
update table2
set a= case x.cat when 'xyz' then x.value end,
b= case x.cat when 'def' then x.value end
c= case x.cat when 'ged' then x.value end
from (select value,gid,pid,cat from table1 ) x
where x.gid = table2.gid
and x.pid= table2.pid
this update is not working, only half of the fields are being updated. what is a better way of doing this?
many thanks!
ryan
-
There may be other issues, but from a 5 second look, it appears that you are missing a comma:
update table2
set a= case x.cat when 'xyz' then x.value end,
b= case x.cat when 'def' then x.value end ,
c= case x.cat when 'ged' then x.value end
from (select value,gid,pid,cat from table1 ) x
where x.gid = table2.gid
and x.pid= table2.pid
Good luck
Wayne
-
untested - but try this...not sure why you're updating with x.value when you say in your post that you want to update with x.cat ?!?!
update table2
set a = case x.cat when 'xyz' then x.cat end,
b = case x.cat when 'def' then x.cat end,
c = case x.cat when 'ged' then x.cat end
from table1 x
inner join
table2 on table2.gid = x.gid
and table2.pid= x.pid
**ASCII stupid question, get a stupid ANSI !!!** -
updating with the 'value' is correct, my mistake if i had mentioned i wanted to update with the 'cat'.
what im finding is this: when i join table1 & table2 together (passing the pid & gid) i get 2 values back. the update statement is only taking the last row, using that as the update and the first row is ignored. does this mean i will need to perform 3 update statements? one for each a,b & c?
-
ryan - did you try it with the syntax i sent you - substituting x.cat with x.value of course...you should get 3 updates with that one statement...???
**ASCII stupid question, get a stupid ANSI !!!** -
i tried using the inner join. still only one of the 3 fields is being updated.
referring to my post before this one, because the join returns 2 rows of data, the update statement is only updating one of the 3 values, which is the last row returned in the join.
ie join returns:
gid pid value cat
1 5 55 a
1 5 70 b
and the update is only updating b to the value of 70, a is being left null. i think this means i need to have 3 seperate update statements simply because there will be a maximum of 3 values returned in the above join for each pid/gid
-
would you pl. do me a favour and post the data from each of your tables ?!?!
i ran a quick test on some "dummy" tables and i did get 3 rows updated...
create table t1
(Gid int,
Pid int,
value varchar(5),
Cat char(1))
insert into t1 values(1, 1, 'aloha', 'a')
insert into t1 values(2, 2, 'hello', 'b')
insert into t1 values(3, 3, 'def', 'c')
insert into t1 values(4, 4, 'xyz', 'a')
insert into t1 values(5, 5, 'ged', 'a')
create table t2
(Gid int,
Pid int,
a char(10),
b char(10),
c char(10))
insert into t2 values(1, 1, null, null, null)
insert into t2 values(2, 2, null, null, null)
insert into t2 values(3, 3, null, null, null)
insert into t2 values(6, 1, null, null, null)
insert into t2 values(5, 2, null, null, null)
**ASCII stupid question, get a stupid ANSI !!!** -
To combine multiple rows into a single row, you need to use an aggregate function, which in this case would be MAX.
Here is complete example, but look at the end for
MAX(CASE Table1.cat when 'ABC' then Table1.value else NULL end) as ABC
if object_id('Table1') is not null drop table Table1
go
create table Table1
(Gid char(1) not null
,Pidchar(1) not null
,Catchar(3)not null
,Valuechar(7) not null
, Primary key (Gid, Pid, Cat)
)
go
if object_id('Table2') is not null drop table Table2
go
create table Table2
(Gid char(1) not null
,Pidchar(1) not null
,ABCchar(7)not null
,DEFchar(7)not null
,XYZchar(7)not null
, Primary key (Gid, Pid)
)
go
insert into table1
(Gid, Pid, Cat,Value)
select'A' , '1', 'ABC' ,'A-1-ABC' union all
select'A' ,'1', 'DEF' ,'A-1-DEF' union all
select'A' ,'1', 'XYZ' ,'A-1-XYZ' union all
select'A' ,'2', 'ABC' ,'A-2-ABC' union all
select'A' ,'2', 'DEF' ,'A-2-DEF' union all
select'A' ,'2', 'XYZ' ,'A-2-XYZ' union all
select'B' ,'1', 'ABC' ,'A-1-ABC' union all
select'B' ,'1', 'DEF' ,'A-1-DEF' union all
select'B' ,'1', 'XYZ' ,'A-1-XYZ' union all
select'B' ,'2', 'ABC' ,'A-2-ABC' union all
select'B' ,'2', 'DEF' ,'A-2-DEF' union all
select'B' ,'2', 'XYZ' ,'A-2-XYZ'
go
insert into table2
(Gid ,Pid,ABC ,DEF,XYZ)
select distinct Gid, Pid, '?' ,'!', '@'
from table1
go
update table2
set ABC = T.ABC
, DEF = T.DEF
, XYZ = T.XYZ
FROM (select Gid
, Pid
, MAX(CASE Table1.cat when 'ABC' then Table1.value else NULL end) as ABC
, MAX(CASE Table1.cat when 'DEF' then Table1.value else NULL end) as DEF
, MAX(CASE Table1.cat when 'XYZ' then Table1.value else NULL end) as XYZ
fromTable1
group by Gid, Pid
) as T
whereTable2.Gid = T.Gid
andTable2.Pid= T.Pid
go
select * from table2
SQL = Scarcely Qualifies as a Language
-
thanks for all your help...much appreciated..
table1:
gid pid value cat
1 5 77 a
1 5 3.2 b
1 5 80 c
table2
gid pid a b c
1 5 null null null
im trying to update table2 and only field b is being updated with the value of 3.2. i'd like to have a,b & c all updated at once using the query we've been discussing.
-
ryan - completely misunderstood your question - thought you wanted to update 3 different rows (as opposed to 3 columns in a single row) - only became clear when you posted the sample rows - at any rate, carl's already posted your solution - just make sure you set the ansi warnings off else you'll keep getting the annoying warning msg about null values & aggregate functions...
here's the complete thing (just modified carl's t-sql..thx. carl)
SET ANSI_WARNINGS OFF UPDATE table2 SET a = t1.A, b = t1.b, c = t1.c FROM (SELECT Gid, Pid, a = MAX(CASE t1.cat WHEN 'a' THEN t1.value END), b = MAX(CASE t1.cat WHEN 'b' THEN t1.value END), C = MAX(CASE t1.cat WHEN 'c' THEN t1.value END) FROM table1 GROUP BY Gid, Pid )t1 INNER JOIN table2 ON t1.Gid = table2.Gid AND t1.Pid = table2.Pid
**ASCII stupid question, get a stupid ANSI !!!** -
My experience is that the UPDATE table must also be the FROM table, so here is the tweaked example from sushila.
SET ANSI_WARNINGS OFF
UPDATE table2
SET table2.a = t1.a, table2.b = t1.b ,table2.c = t1.c
FROM table2
INNER JOIN (SELECT Gid, Pid,
a = MAX(CASE cat WHEN 'a' THEN value END),
b = MAX(CASE cat WHEN 'b' THEN value END),
c = MAX(CASE cat WHEN 'c' THEN value END)
FROM table1
GROUP BY Gid, Pid) t1
ON table2.Gid = t1.Gid AND table2.Pid = t1.Pid
Andy
-
wow! thanks for the answer guys. this solution worked perfectly. i would have never thought of doing this. i still dont quite understand how using the max function is making this work. is it an easy explanation?
-
Ryan - I'll take a stab at it - though things are often clear inside my own head I may lose something in the translation...
Simplest explanation I can think of:
If you compare a "select * from table1" with the "select max(case t1.cat....)" you will see that the first
select returns 3 rows and the 2nd one returns just a single row - the second t-sql is part of a derived table where - in order to combine the 3 rows into one - a "group by" has to be done on the "join columns" - (Gid & Pid) - in order for the "group by" to work, we must have aggregate functions on all the columns that we are selecting (unless we're grouping by them...) - hence the max on the t1.cat and t1.value...
Since you have to update a single row - multiple columns : the derived table returns just that -
Hopefully I've dispelled at least a little of the confusion ?!?! Otherwise, maybe someone else will come along with a simpler, more comprehensive explanation than mine!!!
**ASCII stupid question, get a stupid ANSI !!!**
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply