根据先前查询的结果更新表

如何根据上一个查询的结果更新表？

The 原始查询（非常感谢GMB）可以找到地址（users 表）中与地址（address_effect 表）匹配的任何项目。

从这个查询的结果中，我想找到address_effect表中的地址计数，并将其添加到表“users”中的新列中。例如，john doe 在地址列中与 idaho 和 usa 匹配，因此计数列中会显示计数“2”。

仅供参考，我正在我的本地系统上使用 XAMPP（使用 MariaDB）进行测试。

用户表

+--------+-------------+---------------+--------------------------+--------+
|    ID  |  firstname  |  lastname     |    address               |  count |
|        |             |               |                          |        |
+--------------------------------------------------------------------------+
|     1  |    john     |    doe        |james street, idaho, usa  |        |                    
|        |             |               |                          |        |
+--------------------------------------------------------------------------+
|     2  |    cindy    |   smith       |rollingwood av,lyn, canada|        |
|        |             |               |                          |        |
+--------------------------------------------------------------------------+
|     3  |    rita     |   chatsworth  |arajo ct, alameda, cali   |        |
|        |             |               |                          |        |
+--------------------------------------------------------------------------+
|     4  |    randy    |   plies       |smith spring, lima, peru  |        |                       
|        |             |               |                          |        |
+--------------------------------------------------------------------------+
|     5  |    Matt     |   gwalio      |park lane, atlanta, usa   |        |
|        |             |               |                          |        |
+--------------------------------------------------------------------------+

地址效果表

+---------+----------------+
|address   |effect         |
+---------+----------------+
|idaho    |potato, tater   |
+--------------------------+
|canada   |cold, tundra    |
+--------------------------+
|fremont  | crowded        |
+--------------------------+
|peru     |alpaca          |
+--------------------------+
|atlanta  |peach, cnn      |
+--------------------------+
|usa      |big, hard       |
+--------+-----------------+

使用相关子查询返回匹配数：

UPDATE user u
SET u.count = (
  SELECT COUNT(*)
  FROM address_effect a
  WHERE FIND_IN_SET(a.address, REPLACE(u.address, ', ', ','))
)

See the demo.
结果：

> ID | firstname | lastname   | address                    | count
> -: | :-------- | :--------- | :------------------------- | ----:
>  1 | john      | doe        | james street, idaho, usa   |     2
>  2 | cindy     | smith      | rollingwood av,lyn, canada |     1
>  3 | rita      | chatsworth | arajo ct, alameda, cali    |     0
>  4 | randy     | plies      | smith spring, lima, peru   |     1
>  5 | Matt      | gwalio     | park lane, atlanta, usa    |     2