MySQL:循环遍历一个表，然后更新另一个表?

Table t1:

表t1:

s (string)      |  x (int)
----------------+--------
"gfrdgeradfg"   |  0
"abdfodpnmn"    |  0
...             |  ...

Table t2:

表t2:

c (varchar(1))
-----
"a"  
"c"  
"g"
"r"  
-----

I would like to add +1 to t1.x for every character t2.c that occurs in t1.s, i.e. the result should be something like this:

我想把+1加到t1。x表示每个t。c发生在t1。s，即结果应该是这样的:

s               |  x
----------------+--------
"gfrdgeradfg"   |  3      (contains "a","g","r")
"abdfodpnmn"    |  1      (contains "a")
...             |  ...

Looping through t2 and update t1 in php is quite straightforward, but I'd rather do it in pure SQL, if possible.

在php中循环遍历t2和更新t1非常简单，但如果可能的话，我宁愿使用纯SQL。

Thanks for your help.

谢谢你的帮助。

1 个解决方案

#1

UPDATE t1
SET x = (
    SELECT SUM(t1.s LIKE CONCAT('%', t2.c, '%'))
    FROM t2
)

Clarification: The expression t1.s LIKE CONCAT('%', t2.c, '%') will evaluate to a Boolean which is equivalent to 1 or 0 in MySQL.

澄清:t1的表达式。CONCAT(“%”,t2。c， '%')将计算一个布尔值，它在MySQL中等于1或0。

I didn't test it so please tell me if it doesn't work.

我没有测试它，所以请告诉我它是否不起作用。

#1

UPDATE t1
SET x = (
    SELECT SUM(t1.s LIKE CONCAT('%', t2.c, '%'))
    FROM t2
)