mysql - MySQL复制单个表内的数据，具体取决于列

我有一个这样的MySQL表：

post_id, meta_id,  meta_value
0000001, counter1, 00023
0000001, counter2, 00003
0000002, counter1, 00035
0000002, counter2, 00555
0000003, counter1, 00452
0000003, counter2, 00533
.
.
.
0007888, counter1, 04520
0007888, counter2, 00233

对于每个Post_ID，我想将meta_id = counter2的行的值更改为与meta_id = counter1的行的值相同，如下所示：

post_id, meta_id,  meta_value
0000001, counter1, 00023
0000001, counter2, 00023
0000002, counter1, 00035
0000002, counter2, 00035
0000003, counter1, 00452
0000003, counter2, 00452
.
.
.
0007888, counter1, 04522
0007888, counter2, 04522

有任何想法吗？谢谢。

最佳答案

这可能有帮助

SQL Fiddle

MySQL 5.6模式设置：

CREATE TABLE t
    (`post_id` int, `meta_id` varchar(8), `meta_value` int)
;

INSERT INTO t
    (`post_id`, `meta_id`, `meta_value`)
VALUES
    (0000001, 'counter1', 00023),
    (0000001, 'counter2', 00003),
    (0000002, 'counter1', 00035),
    (0000002, 'counter2', 00555),
    (0000003, 'counter1', 00452),
    (0000003, 'counter2', 00533)
;

update t
join t as tin
  on tin.post_id = t.post_id and
     tin.meta_id != t.meta_id
set t.meta_value = tin.meta_value
where t.meta_id = 'counter2';

查询1：

select * from t

Results：

| post_id |  meta_id | meta_value |
|---------|----------|------------|
|       1 | counter1 |         23 |
|       1 | counter2 |         23 |
|       2 | counter1 |         35 |
|       2 | counter2 |         35 |
|       3 | counter1 |        452 |
|       3 | counter2 |        452 |

关于mysql - MySQL复制单个表内的数据，具体取决于列，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/54659798/