0

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).

  • The row selection is based on a condition in an unshared column (Column3) in another table (Table2).
  • Table1 and another table (Table2) share a column (Column2).
  • Some rows in Column1 currently contain null values.

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.

Antony
  • 81
  • 7
  • 1
    To know if something would work you could always test it with a test DB/tables. This question also is asked a 100 times already. You could use search :) – MBijen Feb 09 '18 at 13:56
  • 1
    Possible duplicate of [SQL UPDATE SET one column to be equal to a value in a related table referenced by a different column?](https://stackoverflow.com/questions/707371/sql-update-set-one-column-to-be-equal-to-a-value-in-a-related-table-referenced-b) – MBijen Feb 09 '18 at 13:58
  • 1
    Why are u using `right join`, any specific reason for that – Ven Feb 09 '18 at 14:33
  • In my experience, there are very few cases where a `RIGHT JOIN` can't be rewritten as another type of `JOIN` that is easier to read. When I see them, it's usually an indication that the query needs to be rethought and refactored. Same as seeing an `ORDER BY` in a view. – Shawn Feb 09 '18 at 14:40
  • Having tested the `SELECT` statement using `INNER JOIN`, it doesn't return all of the rows I require. I believe this is due to some having null values in Column1. `RIGHT JOIN`, however, returns all of the values. I sort of stumbled across this so I'm sure there is a more elegant way of solving this than my own. – Antony Feb 09 '18 at 15:43
  • @Antony "Would something like this work?". Have you tried running it to find out if it worked or not??? – Eric Feb 09 '18 at 17:11

2 Answers2

2

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'
Vidmantas Blazevicius
  • 4,652
  • 2
  • 11
  • 30
1

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

sindhu_sp
  • 749
  • 5
  • 12