UPDATE containing subquery error

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 2005 Forums

Transact-SQL (2005)

UPDATE containing subquery error

Author

Topic

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2008-01-11 : 20:48:21

I need to update a table with values from a random row in another table. The select in ths subquery returns a single random row when I run the select stand-alone. But when I add it as a subquery in the update I get the error listed below. What is the correct syntax or method to do this?

UPDATE YSeed_OrderSeeds
SET YSeed_10555DecoyAddressesID = a.YSeed_10555DecoyAddressesID
, Address = a.Address
, Address2 = a.Address2
, City = a.City
, State = a.State
, Zip = a.Zip
where Address is null
AND (SELECT TOP 1 a.YSeed_10555DecoyAddressesID, a.Address, a.Address2, a.City, a.State, a.Zip
FROM YSeed_10555DecoyAddresses a
WHERE a.State = 'ca'
ORDER BY NEWID())

Msg 4145, Level 15, State 1, Line 12
An expression of non-boolean type specified in a context where a condition is expected, near ')'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-11 : 23:52:29

Try:-

UPDATE y
SET y.YSeed_10555DecoyAddressesID = t.YSeed_10555DecoyAddressesID
, y.Address = t.Address
, y.Address2 = t.Address2
, y.City = t.City
, y.State = t.State
, y.Zip = t.Zip
FROM YSeed_OrderSeeds y
CROSS JOIN (SELECT TOP 1 a.YSeed_10555DecoyAddressesID, a.Address, a.Address2, a.City, a.State, a.Zip
FROM YSeed_10555DecoyAddresses a
WHERE a.State = 'ca'
ORDER BY NEWID())t
where y.Address is null

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2008-01-12 : 00:22:40

That works! Thank you

I would never thought to use a CROSS JOIN.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources