1

I would like to run an Update on a table based upon values in another table.

So I have Table 1 with Column A

And I have Table 2 with Column B

I want to run an update so that every row in Column A gets updated with 'RANDOM STRING' if Column A is LIKE Column B.

Pretty simple up to here. However the string in Column B could occur anywhere in the String in Column A.

So the query should run something like this

UPDATE Table1
SET ColumnA = 'RANDOM STRING'
WHERE ColumnA LIKE '%Table2.ColumnB%'

However no rows get updated when I use this, though the WHERE condition should definitely return results

I am running SQL server 2008

TechDo
  • 18,398
  • 3
  • 51
  • 64
Eish
  • 1,051
  • 1
  • 9
  • 16
  • possible duplicate of http://stackoverflow.com/questions/982919/sql-update-query-using-joins .. or search for Update with Join will solve your problem. – Sumit Gupta Oct 18 '13 at 05:53
  • you are comparing the values from ColumnA to a string "%Table2.ColumnB%" – Gutanoth Oct 18 '13 at 05:54
  • @Gutanoth thanks for the reply. How do I reference another column instead of a string. If I remove the apostrophe it returns an incorrect syntax error – Eish Oct 18 '13 at 05:56
  • @SumitGupta it's not a duplicate. That other question has 3 joins and is not trying to reference a Like to another Table and another column. It's not even close to being a duplicate – Eish Oct 18 '13 at 05:57
  • How about doing a SELECT first, then try the UPDATE ? – Leptonator Oct 18 '13 at 05:58

2 Answers2

3

try this

update table1
set  ColumnA = 'RANDOM STRING'
where ColumnA in  
(select table1.ColumnA from table1 inner join table2 on table1.ColumnA like '%'+Table2.ColumnB+'%')
Nitu Bansal
  • 3,826
  • 3
  • 18
  • 24
0

In MySql, You can do this.

UPDATE Table1
SET ColumnA = 'RANDOM STRING' from table1 , table2
WHERE table1.ColumnA LIKE concat('%',table2.columnb,'%')
kanchan
  • 339
  • 1
  • 3
  • 15