I would like to run an Update on a table based upon values in another table.
我想基于另一个表中的值在表上运行更新。
So I have Table 1 with Column A
所以我在表1中列出了A栏
And I have Table 2 with Column B
我有表B和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.
我想运行更新,以便如果A列是LIKE B列,则A列中的每一行都会更新为'RANDOM STRING'。
Pretty simple up to here. However the string in Column B could occur anywhere in the String in Column A.
这里很简单。但是,B列中的字符串可能出现在A列的String中的任何位置。
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
但是当我使用它时没有行更新,尽管WHERE条件肯定会返回结果
I am running SQL server 2008
我正在运行SQL Server 2008
2 个解决方案
#1
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+'%')
#2
0
In MySql, You can do this.
在MySql中,您可以这样做。
UPDATE Table1
SET ColumnA = 'RANDOM STRING' from table1 , table2
WHERE table1.ColumnA LIKE concat('%',table2.columnb,'%')
#1
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+'%')
#2
0
In MySql, You can do this.
在MySql中,您可以这样做。
UPDATE Table1
SET ColumnA = 'RANDOM STRING' from table1 , table2
WHERE table1.ColumnA LIKE concat('%',table2.columnb,'%')