CODEWITHSUNDEEP
sqlsql-server
Eish
Eish

Reputation: 1081

How do I Update a column that is like %another column% in SQL

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

Upvotes: 1

Views: 4134

Answers (2)

kanchan
kanchan

Reputation: 359

In MySql, You can do this.

UPDATE Table1
SET ColumnA = 'RANDOM STRING' from table1 , table2
WHERE table1.ColumnA LIKE concat('%',table2.columnb,'%')

Upvotes: 0

Nitu Bansal
Nitu Bansal

Reputation: 3856

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+'%')

Upvotes: 3

Related Questions