Problem re: Updates - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Problem re: Updates

Author

Topic

youruseridistoxic
Yak Posting Veteran

65 Posts

Posted - 2009-04-16 : 09:30:48

Hello,

I need to update the values of table1 with the values from table2, except table2 contains multiple values for each FK that exists in table1.

A sample dataset from table1;

User_ID    PrimaryExt     SecondaryExt     ThirdExt
0014       NULL           NULL             NULL
0019       NULL           NULL             NULL
0286       NULL           NULL             NULL

A sample dataset from table2;

User_ID     Alternate_ID
0014        50514
0014        58514
0019        50750
0019        56750
0286        51632
0286        52632
0286        53632

My expected output would be as follows;

User_ID    PrimaryExt     SecondaryExt     ThirdExt
0014       50514           58514             NULL
0019       50750           56750             NULL
0286       51632           52632             53632

I've never tried anything quite this complex before. Can anyone assist or provide some suggestions on how to best accomplish the desired result?

Many thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-16 : 09:56:43

Are you using SQL Server 2005?

DECLARE	@Sample TABLE
	(
		UserID CHAR(4),
		AlternateID INT
	)

INSERT	@Sample
SELECT	'0014', 50514 UNION ALL
SELECT	'0014', 58514 UNION ALL
SELECT	'0019', 50750 UNION ALL
SELECT	'0019', 56750 UNION ALL
SELECT	'0286', 51632 UNION ALL
SELECT	'0286', 52632 UNION ALL
SELECT	'0286', 53632

SELECT	p.UserID,
	p.[1] AS PrimaryExt,
	p.[2] AS SecondaryExt,
	p.[3] AS ThirdExt
FROM	(
		SELECT	UserID,
			AlternateID,
			ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY AlternateID) AS colID
		FROM	@Sample
	) AS s
PIVOT	(
		MAX(AlternateID)
		FOR colID IN ([1], [2], [3])
	) AS p

E 12°55'05.63"
N 56°04'39.26"

youruseridistoxic
Yak Posting Veteran

65 Posts

Posted - 2009-04-16 : 10:19:41

quote:

Originally posted by Peso

Are you using SQL Server 2005?

DECLARE	@Sample TABLE
	(
		UserID CHAR(4),
		AlternateID INT
	)

INSERT	@Sample
SELECT	'0014', 50514 UNION ALL
SELECT	'0014', 58514 UNION ALL
SELECT	'0019', 50750 UNION ALL
SELECT	'0019', 56750 UNION ALL
SELECT	'0286', 51632 UNION ALL
SELECT	'0286', 52632 UNION ALL
SELECT	'0286', 53632

SELECT	p.UserID,
	p.[1] AS PrimaryExt,
	p.[2] AS SecondaryExt,
	p.[3] AS ThirdExt
FROM	(
		SELECT	UserID,
			AlternateID,
			ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY AlternateID) AS colID
		FROM	@Sample
	) AS s
PIVOT	(
		MAX(AlternateID)
		FOR colID IN ([1], [2], [3])
	) AS p

E 12°55'05.63"
N 56°04'39.26"

Thanks for the reply!

Yes, I am using SQL2K5. The solution provided works exactly as I had hoped...very nice.

Now, Table1 actually holds over 1300 unique records (for user_id).

Do I need to list each one of them with a corresponding UNION operator??

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-04-16 : 10:23:25

That is just a table declaration that Peso has used to work with sample data.

Use your actual table name instead of "@Sample" in the code.

quote:
ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY AlternateID) AS colID FROM @Sample

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-04-16 : 10:23:55

My solution incl. update table1

--A sample dataset from table1;


--User_ID    PrimaryExt     SecondaryExt     ThirdExt
--0014       NULL           NULL             NULL
--0019       NULL           NULL             NULL
--0286       NULL           NULL             NULL


--A sample dataset from table2;

--User_ID     Alternate_ID
--0014        50514
--0014        58514
--0019        50750
--0019        56750
--0286        51632
--0286        52632
--0286        53632


--My expected output would be as follows;

--User_ID    PrimaryExt     SecondaryExt     ThirdExt
--0014       50514           58514             NULL
--0019       50750           56750             NULL
--0286       51632           52632             53632

-- Here we create sample data
create table #table1(
User_Id varchar(4),
PrimaryExt int,
SecondaryExt int,
ThirdExt int)

create table #table2(
User_ID varchar(4),
Alternate_ID int)

insert #table1
select '0014',null,null,null
union all
select '0019',null,null,null
union all
select '0286',null,null,null

insert #table2
select '0014',50514
union all
select '0014',58514
union all
select '0019',50750
union all
select '0019',56750
union all
select '0286',51632
union all
select '0286',52632
union all
select '0286',53632

select * from #table1
select * from #table2

-- here begins my solution
;with my_cte as (
select USER_ID, 
       Alternate_ID, 
       ROW_NUMBER() over (PARTITION by User_ID
                          order by User_Id, Alternate_ID) as Extnum
from #table2)
update t1
set PrimaryExt = pe.Alternate_ID,
SecondaryExt = ps.Alternate_ID,
ThirdExt = pt.Alternate_ID
from #table1 as t1
left join my_cte as pe on (pe.User_ID = t1.User_Id and pe.Extnum = 1)
left join my_cte as ps on (ps.User_ID = t1.User_Id and ps.Extnum = 2)
left join my_cte as pt on (pt.User_ID = t1.User_Id and pt.Extnum = 3)

-- here ends my solution
select * from #table1
drop table #table1
drop table #table2

Greetings
Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-16 : 14:21:16

That will work too WebFred.
You may want to compare the execution plans to see the differences in approaches.

E 12°55'05.63"
N 56°04'39.26"

youruseridistoxic
Yak Posting Veteran

65 Posts

Posted - 2009-04-16 : 14:58:20

quote:
Originally posted by Peso

That will work too WebFred.
You may want to compare the execution plans to see the differences in approaches.

E 12°55'05.63"
N 56°04'39.26"

Thanks to you both! Either of these solutions will work, and I greatly appreciate your input.

If I was to use the first solution however, how would I ammend the syntax of this script to actually perform the update?

Thanks again.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-04-16 : 15:18:38

quote:
Originally posted by Peso

That will work too WebFred.
You may want to compare the execution plans to see the differences in approaches.

E 12°55'05.63"
N 56°04'39.26"

Hi Peso,
I have done the compare.
The picture of my execution plan is more exciting

I have never used PIVOT, so I started to do it my way...
All the time I was sure that you gave an excellent approach, but I could not resist.

Greetings from Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

youruseridistoxic
Yak Posting Veteran

65 Posts

Posted - 2009-04-17 : 14:14:26

I have run into a major problem in applying this update.

I was not initially provided complete access to the database schema, and as a result my first sample dataset was incomplete. I apologize for this. Below is the complete table structure and sample data from each Table1 & Table2;

dbo.Table1;

User_ID          UserName     networkID     email         PrimaryExt     SecondaryExt     ThirdExt     FourthExt     FifthExt
0014             JSmith       jsmith        jsmith@email  NULL           NULL             NULL         NULL          NULL
0019             AJones       ajones        ajones@email  NULL           NULL             NULL         NULL          NULL
0286             BCarr        bcarr         bcarr@email   NULL           NULL             NULL         NULL          NULL

dbo.table2 also had additional criteria;

Profile_Type     User_ID     Alternate_ID
01               0014        50514
01               0014        58514
01               0019        50750
01               0019        56750
01               0286        51632
01               0286        52632
01               0286        53632
02               0014        1@1
02               0019        1@1
02               0286        1@1

Whenever I apply either of the update samples (graciously!) provided with your assistance, I end up with 1@1 values in dbo.table1.

*sigh*

I've been trying to make this work for 2 days now. Slowly losing my mind over this.

Any further assistance with this is greatly appreciated!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources