mysql - 如何设置同一个表中某些行的列值与其他行的列值相同？

Question

表架构:

+----------------------+---------------+------+-----+---------+-------+
| Field                | Type          | Null | Key | Default | Extra |
+----------------------+---------------+------+-----+---------+-------+
| name                 | varchar(10)   | YES  | MUL | NULL    |       |
| slno                 | varchar(20)   | YES  |     | NULL    |       |
| type                 | int(2)        | YES  |     | NULL    |       |
| details              | text          | YES  |     | NULL    |       |
+----------------------+---------------+------+-----+---------+-------+

name、slno 和 type 一起形成一个键。

示例数据:

+---------+------+------+-------------------------------+
| name    | slno | type | details                       |
+---------+------+------+-------------------------------+
| name1   | 11   |    1 | {"data":["feats1","feats2"] } |
| name1   | 11   |    2 | {"data":["feats1","feats2"] } |
| name1   | 12   |    1 | {"data":["feats5","feats6"] } |
| name1   | 12   |    2 | {"data":["feats5","feats6"] } |
| name2   | 11   |    1 | {"data":["feats3","feats4"] } |
| name2   | 11   |    2 | {"data":["feats3","feats4"] } |
| name2   | 12   |    1 | {"data":["feats7","feats8"] } |
| name2   | 12   |    2 | {"data":["feats10"] }         |
+---------+------+------+-------------------------------+

所以基本上对于每个 name = 'name1' 的条目，都有一个具有相同 slno 但 name = 'name2' 的类似条目。
我想要做的是将具有相同 slno 和类型但名称不同的行的详细信息设置为相同，即上面的示例数据集应如下所示。如果 name2 行的 slno 和类型相同，则它们的详细信息应与 name1 行的详细信息匹配。

+---------+------+------+-------------------------------+
| name    | slno | type | details                       |
+---------+------+------+-------------------------------+
| name1   | 11   |    1 | {"data":["feats1","feats2"] } |
| name1   | 11   |    2 | {"data":["feats1","feats2"] } |
| name1   | 12   |    1 | {"data":["feats5","feats6"] } |
| name1   | 12   |    2 | {"data":["feats5","feats6"] } |
| name2   | 11   |    1 | {"data":["feats1","feats2"] } |
| name2   | 11   |    2 | {"data":["feats1","feats2"] } |
| name2   | 12   |    1 | {"data":["feats5","feats6"] } |
| name2   | 12   |    2 | {"data":["feats5","feats6"] } |
+---------+------+------+-------------------------------+

我尝试过，但无法想出执行上述结果的命令。有人可以帮忙吗？

Answer 1

如果要求为 name2 详细信息提供 name1 详细信息中的值，您可以在 MySQL 中通过基于 join 的更新来完成此操作

update  yourTable as t1
join    yourTable as t2
on      t1.slno = t2.slno and
        t1.type = t2.type
set     t2.details = t1.details
where   t2.name = 'name2' and
        t1.name = 'name1'

您也可以将我在 where 子句中放入的条件移动到 join 条件中来实现相同的效果

update  yourTable as t1
join    yourTable as t2
on      t1.slno = t2.slno and
        t1.type = t2.type and
        t2.name = 'name2' and
        t1.name = 'name1'
set     t2.details = t1.details

您可以看到两个正在执行的查询 here