Update w/subquery - 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 w/subquery

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-04 : 08:06:50

Eric writes "Is it possible to update a record with more than one columns using a subquery? I can update a single column, but not more than one.

The following works

UPDATE Subscriber_Addresses SET FirstName =
(SELECT FirstName FROM TransactionFeed_Addresses WHERE AddressID = 1) WHERE (AddressID = 3)

However, the following does not:

UPDATE Subscriber_Addresses SET (FirstName, LastName) =
(SELECT FirstName, LastName FROM TransactionFeed_Addresses WHERE AddressID = 1) WHERE (AddressID = 3)

Is this possible or am I using incorrect syntax?

Thanks
Eric Sturtz"

Nazim
A custom title

1408 Posts

Posted - 2002-04-04 : 08:13:07

UPDATE Subscriber_Addresses SET FirstName= (SELECT FirstName FROM TransactionFeed_Addresses WHERE AddressID = 1),
lastname=(select LastName FROM TransactionFeed_Addresses WHERE AddressID = 1 ) WHERE (AddressID = 3)

HTH

--------------------------------------------------------------

damcalcan
Starting Member

11 Posts

Posted - 2002-04-04 : 17:23:43

or using joins :

UPDATE SA
SET FirstName = TA.FirstName , LastName = TA.LastName
FROM Subscriber_Addresses SA
JOIN TransactionFeed_Addresses TA
ON SA.AddressID = 3 AND TA.AddressID =1

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2002-04-09 : 11:07:22

>> Eric writes "Is it possible to update a record with more than one columns using a subquery? I can update a single column, but not more than one. <<

That syntax is available in SQL-92, via row constructors, but not in SQL Server yet.

--CELKO--
Joe Celko, SQL Guru

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources