help with sql query a query

hiya i have a massive job trying to sort thousands of records all is going okay, but i have a table of business and a table of users, now all the users need to have there business id applied. I have done this:

select b.id as businessid, u.id as userid
from business b, [trade_tools].[dbo].[user] u
where pcode = u.pword AND u.parent_id is null AND u.usertype_id = 5 AND u.display = 1 AND b.display = 1

this works great but i want to wrap this inside say:

update [trade_tools].[dbo].[user]
set parent_id = (select businessid FROM (select b.id as businessid, u.id as userid
from business b, [trade_tools].[dbo].[user] u
where pcode = u.pword AND u.parent_id is null AND u.usertype_id = 5 AND u.display = 1 AND b.display = 1) where userid =       currentIDWeoOnNow)

now i know this is possible i am just not working it the right way, could any SQL Pro's just lend a bit of a hand here so i can get this data to flow.

i did try running an update with an inner select however this did not work because the innrer select created more than 2 results when you are only allowed one, hence going down this route to try and get around that issue.

thanks p.s Server is MSSQL server and i am using SQL Server Management Studio to run m开发者_StackOverflow社区y queries.

1. you have a one-to-many relationship from business to user (i.e. each business has many users but each user only belongs to one business)
2. you are using user.pword and business.pcode to join users to their business

In that case, doing simply this might work:

update [trade_tools].[dbo].[user]
set parent_id = businessid  
from business b, [trade_tools].[dbo].[user] u
where pcode = u.pword AND u.parent_id is null AND u.usertype_id = 5 AND u.display = 1 AND b.display = 1 

I think this might work this time::

update [trade_tools].[dbo].[user] set parent_id = b.id

from business b, [trade_tools].[dbo].[user] u where pcode = u.pword AND u.parent_id is null AND u.usertype_id = 5 AND u.display = 1 AND b.display = 1 and u.userid =currentIDWeoOnNow

Try this:

update [trade_tools].[dbo].[user] 
set parent_id = b.id

from 
business b, [trade_tools].[dbo].[user] u 
where pcode = u.pword 
AND u.parent_id is null 
AND u.usertype_id = 5 
AND u.display = 1 
AND b.display = 1 

where userid =currentIDWeoOnNow