Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Update Query Expand / Collapse
Author
Message
Posted Friday, March 7, 2014 9:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 1:30 AM
Points: 4, Visits: 22
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 NULL NULL


Table2:
ID Value
1 10
2 20
3 30
4 12
5 32
6 12

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
Post #1548788
Posted Friday, March 7, 2014 9:39 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 5:49 AM
Points: 424, Visits: 1,320
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?
Post #1548791
Posted Friday, March 7, 2014 10:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 1:30 AM
Points: 4, Visits: 22
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
Post #1548799
Posted Friday, March 7, 2014 11:27 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:39 AM
Points: 470, Visits: 483
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


Post #1548837
Posted Friday, March 7, 2014 12:31 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 12:21 PM
Points: 76, Visits: 291
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
SET t1.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


  Post Attachments 
Data_Display.jpg (5 views, 75.92 KB)
Post #1548862
Posted Friday, March 7, 2014 4:09 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:19 PM
Points: 35,218, Visits: 31,675
vijaykumar587 (3/7/2014)
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


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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1548922
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse