CODEWITHSUNDEEP
sqlsql-server
Antony
Antony

Reputation: 81

UPDATE a selection of rows to a chosen value based on a condition in another table

I would like to update a selection of rows from a column (Column1) in a table (Table1) with a value of my choosing (i.e. not a value from another table).

I've created a SELECT statement that pulls up the correct rows, which is as follows:

SELECT Table1.Column1, Table2.Column2
FROM Table1
RIGHT JOIN Table2 ON Table1.Column2 = Table2.Column2 
WHERE Table2.Column3 = 'Condition'

However, as I'm very new to SQL, I'm unsure how to turn this into an UPDATE statement that updates Column1 into a value of my choosing.

Would something like this work?

UPDATE Table1
SET Table1.Column1 = 'Value'
FROM Table1
RIGHT JOIN Table2 ON Table1.Column2 = Table2.Column2
WHERE Table2.Column3 = 'Condition'

Many thanks.

Upvotes: 0

Views: 1337

Answers (2)

sindhu_sp
sindhu_sp

Reputation: 759

Try this query:

UPDATE Table1
SET Column1 = t2.Column3
FROM 
Table1 t1 INNER JOIN Table2 t2 ON t1.Column2 = t2.Column2
WHERE t2.Column3 = 'Condition'

As Vidmantas pointed out, INNER JOIN is more appropriate for your case, you don't need a RIGHT JOIN

Upvotes: 1

Vidmantas Blazevicius
Vidmantas Blazevicius

Reputation: 4812

Simple inner join update query should work. I don't see why you would use RIGHT JOIN

UPDATE T1
SET T1.Column1 = 'Value'
FROM Table1 T1
INNER JOIN Table2 T2 ON T1.Column2 = T2.Column2
AND T2.Column3 = 'Condition'

Upvotes: 3

Related Questions