simple update record 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

SQL Server 2000 Forums

Transact-SQL (2000)

simple update record question

Author

Topic

nic
Posting Yak Master

209 Posts

Posted - 2003-11-07 : 17:04:14

Hi,
I'm at a loss on how to do this (I'm sure the answer is very simple). I need to update a record in a table with a different record in the same table.


UPDATE Persons SET 
	FirstName = b.FirstName
	,MiddleName = b.MiddleName
	,LastName = b.LastName	
	,Suffix = b.Suffix
FROM 
	Persons, Persons b
WHERE
	PersonID = 158 -- new record
	AND b.PersonID = 159 -- original record

Essentially I need to override the 158 record with data from the 159 record. I keep getting an Ambiguous column error

Nic

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-07 : 17:23:41

[code]
create table #test (n int,data char(4))
go
insert into #test select 0,'DAT0' union select 1,'DAT1'
select * from #test

update #test set data = a.data from #test t1,(select t2.data from #test t2 where t2.n = 1) a where t1.n =0
go
select * from #test
drop table #test
[/code]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-07 : 17:42:42

[code]

UPDATE Persons
SET
FirstName = b.FirstName
,MiddleName = b.MiddleName
,LastName = b.LastName
,Suffix = b.Suffix
FROM
Persons a, (SELECT FirstName, MiddleName, LastName, Suffix FROM Persons WHERE PersonID = 158) b
WHERE a.PersonID = 159

[/code]

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources