Use join in update query instead of columns when updating rows

Question

更新之前（原始示例表）：

document_id	meta_key	meta_value
1	iban	IBAN123456
1	bankaccount	ACCT987654
2	iban	IBAN555555
2	bankaccount	ACCT444444
3	iban	IBAN888888
3	bankaccount	ACCT333333

运行SQL更新查询后：

document_id	meta_key	meta_value
1	iban	IBAN123456
1	bankaccount	IBAN123456
2	iban	IBAN555555
2	bankaccount	IBAN555555
3	iban	IBAN888888
3	bankaccount	IBAN888888

我需要一个查询来实现上述表格的结果吗？

Answer 1

document_id, meta_key, and meta_value. You want to update the meta_value of the row whose meta_key is bankaccount to the row whose meta_key is iban. The corresponding meta_value.

The following is a SQL query to achieve this goal:

UPDATE example_table AS p1
INNER JOIN (
    SELECT document_id, meta_value AS iban
    FROM example_table
    WHERE meta_key = 'iban'
) AS p2 ON p1.document_id = p2.document_id
SET p1.meta_value = p2.iban
WHERE p1.meta_key = 'bankaccount';

1. The p1 table is an alias of example_table, and p2 is the alias of the subquery
2. The subquery selects the document_id and meta_value when meta_key is 'iban'
3. Used by the main query INNER JOIN to match document_id between p1 and subquery p2.
4. Then, it updates the meta_value whose meta_key is 'bankaccount' in p1 to iban in p2.

Before running any update queries, remember to back up your database and test it in a secure environment!