SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Update Query


Update Query

Author
Message
vijaykumar587
vijaykumar587
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 53
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
higgim
higgim
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1108 Visits: 2622
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?
vijaykumar587
vijaykumar587
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 53
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
SQLCJ
SQLCJ
Mr or Mrs. 500
Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)

Group: General Forum Members
Points: 559 Visits: 576
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



SQLCurious
SQLCurious
SSC-Enthusiastic
SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)

Group: General Forum Members
Points: 199 Visits: 450
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
Attachments
Data_Display.jpg (6 views, 75.00 KB)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84999 Visits: 41072
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search