Using Join on an update query, updating on rows rather than columns

Question

I need a query to achieve second table result, If it is possible, Please describe it in SQL SERVER and MYSQL database I want to update the meta_value of rows where meta_key is bankaccount with the corresponding meta_value from rows where meta_key is iban.

Before the update (original example_table):

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

After running the SQL update query:

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

You want to update the meta_value of rows where meta_key is bankaccount with the corresponding meta_value from rows where meta_key is iban.

Here's a SQL query to achieve this - MySQL:

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';

SQL Server:

UPDATE p1
SET p1.meta_value = p2.iban
FROM 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
WHERE p1.meta_key = 'bankaccount';

Explanation:

1. The p1 table is the alias for example_table, and p2 is the alias for the subquery.
2. The subquery selects document_id and meta_value where meta_key is 'iban'.
3. The main query uses an INNER JOIN to match document_id between p1 and the subquery p2.
4. It then updates meta_value in p1 with the iban from p2 where meta_key is 'bankaccount'.

Remember to back up your database before running any update queries, and test them in a safe environment first!