Update question - 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

Update question

Author

Topic

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2012-08-08 : 06:19:27

Hello there.

I have a list of ref numbers and secondary ref number in a temp table

see below

ref urn

15454 564564654654
56544 878596132188
16546 548546516541

and so on

How would i update the main table with the above table in one script.

using the same column names.

Regards

Rob

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-08-08 : 06:46:08

more information: What if there are duplicates? Is the ref number the primary key? Sounds like you want a MERGE statement, but can't be sure.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-08 : 06:48:36

What you are asking is somewhat vague. Do you want to insert if there are none in the main table? If there are multiple ref numbers do you want to update them all etc. Assuming NO and YES to above questions, respectively,

UPDATE m SET 
	m.urn = t.urn
FROM
	MainTable m
	INNER JOIN TempTable t ON
		t.ref = m.ref;

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2012-08-08 : 06:53:36

thats it sunitbeck

thank you very much.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-08-08 : 07:01:17

here is in case you run into needing to update a record already there, assuming REF as the primary key:



create table #yakperm (ref int, urn bigint)
create table #yaktemp (ref int, urn bigint)

insert into #yakperm
(ref,urn)
values 
	(12345, 968574125896)
,(49492, 293847263465)
,(15454, 564564654654)

insert into #yaktemp
(ref,urn)
values
(15454, 564964694659)
,(56544, 878596132188)
,(16546, 548546516541)

select * from #yakperm 

MERGE #yakperm as Target
	using(select ref,urn from #yaktemp) as source (ref, urn)
on (target.ref = source.ref)
when matched then
	update set urn = source.urn
when not matched then
	insert (ref,urn)
	values (source.ref, source.urn);

select * from #yakperm

drop table #yakperm
drop table #yaktemp

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources