Use join in update query instead of columns when updating rows
P粉170438285
P粉170438285 2023-08-02 12:32:01
0
1
575
<p>更新之前(原始示例表):</p> <table class="s-table"> <thead> <tr> <th>document_id</th> <th>meta_key</th> <th>meta_value</th> </tr> </thead> <tbody> <tr> <td>1</td> <td>iban</td> <td>IBAN123456</td> </tr> <tr> <td>1</td> <td>bankaccount</td> <td>ACCT987654</td> </tr> <tr> <td>2</td> <td>iban</td> <td>IBAN555555</td> </tr> <tr> <td>2</td> <td>bankaccount</td> <td>ACCT444444</td> </tr> <tr> <td>3</td> <td>iban</td> <td>IBAN888888</td> </tr> <tr> <td>3</td> <td>bankaccount</td> <td>ACCT333333</td> </tr> </tbody> </table> <p>运行SQL更新查询后:</p> <table class="s-table"> <thead> <tr> <th>document_id</th> <th>meta_key</th> <th>meta_value</th> </tr> </thead> <tbody> <tr> <td>1</td> <td>iban</td> <td>IBAN123456</td> </tr> <tr> <td>1</td> <td>bankaccount</td> <td>IBAN123456</td> </tr> <tr> <td>2</td> <td>iban</td> <td>IBAN555555</td> </tr> <tr> <td>2</td> <td>bankaccount</td> <td>IBAN555555</td> </tr> <tr> <td>3</td> <td>iban</td> <td>IBAN888888</td> </tr> <tr> <td>3</td> <td>bankaccount</td> <td>IBAN888888</td> </tr> </tbody> </table> <p>我需要一个查询来实现上述表格的结果吗?</p>
P粉170438285
P粉170438285

reply all(1)
P粉826283529

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!


Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template