能否通过单条MERGE语句实现双条件UPDATE更新场景？

Absolutely! You can combine both update scenarios into one MERGE statement, eliminating the need for two separate operations. Here's the optimized query:

MERGE INTO table1 tbl1
USING table2 tbl2
ON (tbl1.id = tbl2.id)
WHEN MATCHED THEN
    UPDATE SET tbl1.phone_number = '123456'
WHEN NOT MATCHED BY SOURCE THEN
    UPDATE SET tbl1.phone_number = '555555';

How this works: #

• WHEN MATCHED clause: This targets all rows where table1.id exists in table2.id (your first update logic). It updates the phone_number to 123456 exactly as your original MERGE statement did.
• WHEN NOT MATCHED BY SOURCE clause: This handles the opposite scenario—rows in table1 that have no matching id in table2. It updates these rows' phone_number to 555555, replacing your second standalone UPDATE query.

Note: This syntax is supported in most modern databases that implement the SQL:2003 standard (like Oracle, SQL Server 2012+, PostgreSQL 15+). If you're working with a specific database that has quirks, you might need minor adjustments, but this core logic holds.

内容的提问来源于stack exchange，提问作者gooner_psy