Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

doeas anyone knows how to update a table where column = @parameter WHERE SELECT

Basically I have a table with the following values. Table name is UserAttribute

ID|Name       |Properties|
17|Description|Student   |
17|Contact    |JohnDoe   |
18|Description|Worker    |
18|Contact    |MaryJane  |

I can update the fields with the following query:

UPDATE UserAttribute 
SET Properties = 'Charlie'
WHERE Name = 'Contact' AND ID = 17

For the results I need I would need a query something like:

UPDATE UserAttribute 
SET Properties = @parameter
WHERE Name = Contact AND ID = (SELECT HouseNo,ID FROM Table2 t1 
JOIN UserAttribute t2 ON t1.ID =t2.UserID
WHERE HouseNo = @HouseNo)

SQL doesnt allows the execution of such a query. Any suggestion on how to write the select statement in the WHERE clause?

Thank you!

EDIT 1: Maybe the question is not the clearest, but its a bit hard to formulate a question when the problem I have is also hard to understand. Therefore I will try to write some kind of pseudo-code, hoping that it will make it a bit more clear.

UPDATE table
SET Column = 'value'
WHERE Column2 = value AND column 3 =(SELECT column from other tables)
share|improve this question
    
the desired result is simply updating the row with the ID, the problem is that I need to do a subquery with a select statement in the WHERE clause, thats what I dont understand how to do –  Greenhorn Mar 12 '13 at 14:53
1  
So how do you expect it to infer the ID you want to update is 17? Is there some logic that you haven't told us? Or are you trying to do it for all Contact rows? –  Martin Smith Mar 12 '13 at 14:56
    
well basically I pass 2 parameters, let's call the one used in this example '@'parameter, and the other one '@'HouseNo so the query should be something like 'WHERE Name = Contact AND ID = (SELECT HouseNo,ID FROM Table2 t1 JOIN UserAttribute t2 ON t1.ID = t2.ID WHERE HouseNo = '@'HouseNo)' –  Greenhorn Mar 12 '13 at 15:02
1  
ID = (SELECT HouseNo,ID FROM Table2 t1 JOIN UserAttribute t2 ON t1.ID =t2.UserID WHERE HouseNo = @HouseNo) will error becasue you are selecting two fields and not one. –  HLGEM Mar 12 '13 at 17:18
add comment

1 Answer

up vote 2 down vote accepted

You're close, but when you introduce two tables, you have to be consistent with the alias. So at the declaration of the update statement, you'd want to start with:

UPDATE ua1 
SET properties = 'Charlie' 

Then create a from clause just as you would with a select statement, declaring your aliases:

FROM UserAttribute ua1 
    INNER JOIN UserAttribute ua2 
        ON ua2.UserID = ua1.id 
WHERE ua1.name = 'Contact';
share|improve this answer
    
Thanks a lot, I will give it a try and return to you –  Greenhorn Mar 12 '13 at 15:10
    
It works like a charm :D Thank you so much for the help, too bad I can't up vote it more than once –  Greenhorn Mar 13 '13 at 6:58
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.