Update Query
-
Hi All,
I need help on updating following tables. Please suggest best way in terms of performance prospective.
Table1:
ROW ID1 ID2 ID3 ID4 ID5 ID6
1 NULL NULL NULL NULL NULLNULL
Table2:
ID Value
110
220
330
412
532
612
I want to update table1 from table2. After update the data should be like below:
Table1:
ROW ID1 ID2 ID3 ID4 ID5 ID6
1 10 20 30 12 32 12
Currently I wrote 6 queries for acheving this. Please refer the below queries:
UPDATE t1 set t1.ID1 = t2.value
from Table1 t1
inner join Table2 t on t2.ID =1
UPDATE t1 set t1.ID2 = t2.value
from Table1 t1
inner join Table2 t on t2.ID =2
UPDATE t1 set t1.ID3 = t2.value
from Table1 t1
inner join Table2 t on t2.ID =3
UPDATE t1 set t1.ID4 = t2.value
from Table1 t1
inner join Table2 t on t2.ID =4
UPDATE t1 set t1.ID5 = t2.value
from Table1 t1
inner join Table2 t on t2.ID =5
UPDATE t1 set t1.ID6 = t2.value
from Table1 t1
inner join Table2 t on t2.ID =6
Please suggest me is there any best way.
Thanks in advance
Vijay
-
having a quick look I can't see how your update queries will work but that said:
If there is more than one entry in Table 1 how do we identify what we should be using from table 2 to update in table 1?
-
I am sorry for that. Actually there is common column is there in two tables. Please check below
Table1:
PID ID1 ID2 ID3 ID4 ID5 ID6
1 NULL NULL NULL NULL NULL NULL
2 NULL NULL NULL NULL NULL NULL
3 NULL NULL NULL NULL NULL NULL
Table2:
PID ID Value
1 1 10
1 2 20
1 3 30
1 4 12
1 5 32
1 6 12
2 1 11
2 2 21
2 3 30
2 4 15
2 5 32
2 6 12
3 1 18
3 2 20
3 3 33
3 4 12
3 5 42
3 6 52
Now my query is:
update t1 set t1.ID1 = t2.value
from Table1 t1
inner join Table2 t on t1.PID = t2.PID AND t2.ID=1
update t1 set t1.ID2 = t2.value
from Table1 t1
inner join Table2 t on t1.PID = t2.PID AND t2.ID=2
.
.
.
.
Thanks in advance,
Vijay
-
Not sure about the performance but tried doing it following way... please consider cte and cte1 as tables wrt to update statement written at the end
;with cte as
(
select 1 as pid, null as id1, null as id2, null as id3
union all
select 2 as pid, null as id1, null as id2, null as id3
),
cte1 as
(
select 1 as pid,1 as id,10 as value
union all
select 1 as pid,2 as id,20 as value
union all
select 1 as pid,3 as id,30 as value
union all
select 2 as pid,1 as id,10 as value
union all
select 2 as pid,2 as id,20 as value
union all
select 2 as pid,3 as id,30 as value
),
cte2 as
(
select distinct pid,
(select value from cte1 c1 where c1.pid = c2.pid and c1.id = 1 ) as id1,
(select value from cte1 c1 where c1.pid = c2.pid and c1.id = 2 ) as id2,
(select value from cte1 c1 where c1.pid = c2.pid and c1.id = 3 ) as id3 from cte1 c2
)
update cte
set id1 = c2.id1,id2 = c2.id2,id3 = c2.id3
from cte2 c2 inner join cte c on c2.pid = c.pid
-
Please check the SQL snippet below and also the attached image for before and after snapshots of the data. I hope it helps.
-- Table1 has just MyID...rest of the columns all have NULL values
DECLARE @Table1 TABLE (MyID INT, ID1 INT, ID2 INT, ID3 INT, ID4 INT)
INSERT @Table1 (MyID, ID1, ID2, ID3, ID4) VALUES (1, NULL, NULL, NULL, NULL), (2, NULL, NULL, NULL, NULL), (3, NULL, NULL, NULL, NULL), (4, NULL, NULL, NULL, NULL)
SELECT * FROM @Table1
-- Table2 has multiple values for MyID; each MyID has multiple ColumnNames (a max of 4, from ID1 thru ID4) and corresponding ColumnValues
DECLARE @Table2 TABLE (MyID INT, ColumnName VARCHAR (10), ColumnValue INT)
INSERT @Table2 VALUES (1, 'ID1', 10), (1, 'ID2', 20), (1, 'ID3', 30), (1, 'ID4', 40), (2, 'ID1', 101), (2, 'ID2', 102), (2, 'ID3', 103), (3, 'ID1', 1001), (3, 'ID3', 1003), (4, 'ID1', 111), (4, 'ID2', 222), (4, 'ID3', 333), (4, 'ID4', 444)
SELECT * FROM @Table2
-- Update Table1 by PIVOTing Table2 to match the structure
UPDATE@Table1
SETt1.ID1 = pvt.ID1,
t1.ID2 = pvt.ID2,
t1.ID3 = pvt.ID3,
t1.ID4 = pvt.ID4
FROM@Table1 t1
INNER JOIN (
SELECT*
FROM@Table2
PIVOT
(AVG(ColumnValue) FOR ColumnName IN ([ID1], [ID2], [ID3], [ID4])
) AS MyVal
) pvt
ON t1.MyID = pvt.MyID
SELECT * FROM @Table1
- RexHelios
-
vijaykumar587 (3/7/2014)
I am sorry for that. Actually there is common column is there in two tables. Please check belowTable1:
PID ID1 ID2 ID3 ID4 ID5 ID6
1 NULL NULL NULL NULL NULL NULL
2 NULL NULL NULL NULL NULL NULL
3 NULL NULL NULL NULL NULL NULL
Table2:
PID ID Value
1 1 10
1 2 20
1 3 30
1 4 12
1 5 32
1 6 12
2 1 11
2 2 21
2 3 30
2 4 15
2 5 32
2 6 12
3 1 18
3 2 20
3 3 33
3 4 12
3 5 42
3 6 52
Now my query is:
update t1 set t1.ID1 = t2.value
from Table1 t1
inner join Table2 t on t1.PID = t2.PID AND t2.ID=1
update t1 set t1.ID2 = t2.value
from Table1 t1
inner join Table2 t on t1.PID = t2.PID AND t2.ID=2
.
.
.
.
Thanks in advance,
Vijay
You'll have much better luck getting answers in the future if you make your tables and sample data in a readily consumable manner instead of just posting plain text like you did. Please see the first article under "Helpful Links" in my signature line below for a method to do that properly.
Since you're a newbie, I'll do it for you this time. The following creates the test data you gave in a test table and then offers a high performance CROSSTAB as a solution to not only populate Table 1, but create it, as well. You shouldn't need an update for this.
--===== Build and populate the test table on the fly.
-- This is NOT a part of the solution. We' just
-- building some test data here.
SELECT PID,ID,[Value]
INTO #Table2
FROM (--==== Test data
SELECT 1,1,10 UNION ALL
SELECT 1,2,20 UNION ALL
SELECT 1,3,30 UNION ALL
SELECT 1,4,12 UNION ALL
SELECT 1,5,32 UNION ALL
SELECT 1,6,12 UNION ALL
SELECT 2,1,11 UNION ALL
SELECT 2,2,21 UNION ALL
SELECT 2,3,30 UNION ALL
SELECT 2,4,15 UNION ALL
SELECT 2,5,32 UNION ALL
SELECT 2,6,12 UNION ALL
SELECT 3,1,18 UNION ALL
SELECT 3,2,20 UNION ALL
SELECT 3,3,33 UNION ALL
SELECT 3,4,12 UNION ALL
SELECT 3,5,42 UNION ALL
SELECT 3,6,52
)d(PID,ID,[Value])
;
--===== Pivot the data using the ancient high performance
-- method of a "CROSSTAB" to build Table 1.
SELECT PID
,ID1 = SUM(CASE WHEN ID=1 THEN [Value] ELSE 0 END)
,ID2 = SUM(CASE WHEN ID=2 THEN [Value] ELSE 0 END)
,ID3 = SUM(CASE WHEN ID=3 THEN [Value] ELSE 0 END)
,ID4 = SUM(CASE WHEN ID=4 THEN [Value] ELSE 0 END)
,ID5 = SUM(CASE WHEN ID=5 THEN [Value] ELSE 0 END)
,ID6 = SUM(CASE WHEN ID=6 THEN [Value] ELSE 0 END)
INTO #Table1
FROM #Table2
GROUP BY PID
;
--===== Display the content of the new Table1
SELECT *
FROM #Table1
;
Results:
PID ID1 ID2 ID3 ID4 ID5 ID6
----------- ----------- ----------- ----------- ----------- ----------- -----------
1 10 20 30 12 32 12
2 11 21 30 15 32 12
3 18 20 33 12 42 52
(3 row(s) affected)
If it absolutely has to be done as an update, then just use the CROSSTAB as a CTE and join to it for the update.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply