SQL update set column = @ parameter

Posted on

Question :

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!

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)

Answer :

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:

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';

Leave a Reply

Your email address will not be published. Required fields are marked *