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

SQL Server 2000 Forums

Transact-SQL (2000)

Update

Author

Topic

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-02-14 : 16:16:17

I'm looking to write a set based update to a table based on another table and itself. I have a denormalized table "card" and need to copy customer info to their new card. The conversion info is in "cardconversion"

create table card
(
cardid int identity(1,1) not null,
firstname varchar(20) null,
lastname varchar(50) null,
dob datetime null
)
go
insert into card values ('Bob', 'Smith', '1/2/2000')
insert into card values ('Jane', 'Smith', '1/5/2000')
insert into card values ('John', 'Doe', '2/5/2000')
insert into card values (NULL, NULL, NULL)
insert into card values (NULL, NULL, NULL)
insert into card values (NULL, NULL, NULL)
go
create table cardconversion
(
oldid int not null,
newid int not null
)
go
insert into cardconversion values (1, 5)
insert into cardconversion values (2, 6)
go

In this example I need to copy the info from card 1 to card 5 and from card 2 to card 6.

Brain dead and open to suggestions.
Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-14 : 17:24:54

[code]update c
set c.firstname = d.firstname,
c.lastname = d.lastname,
c.dob = d.dob
from card c inner join cardconversion v
on c.cardid = v.newid
inner join card d
on v.oldid = d.cardid[/code]

----------------------------------
'KH'
everything that has a beginning has an end

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-15 : 02:16:35

And you dont need to set alias name c to the columns that you are assigning values

update	c
	set	firstname	= d.firstname,
		lastname	= d.lastname,
		dob		= d.dob
	from	card c inner join cardconversion v
		on	c.cardid	= v.newid
	inner join card d
		on	v.oldid	= d.cardid

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources