如何将指定SQL SELECT语句改写为UPDATE语句的技术咨询

First, let's recap your goal: you want to update AUF_ADR.EMAIL to match KUST_ADR.KU_EMAIL for rows that meet the join and filter conditions from your original SELECT query.

Below are examples tailored to common SQL dialects—pick the one that matches your database:

1. MySQL / MariaDB Syntax #

MySQL uses a straightforward JOIN structure directly in the UPDATE statement:

UPDATE AUF_ADR
JOIN AUF_KOPF ON AUF_ADR.AUF_NR = AUF_KOPF.AUF_NR
JOIN AUF_STAT ON AUF_ADR.AUF_NR = AUF_STAT.AUF_NR
JOIN KUST_ADR ON AUF_KOPF.KUNR = KUST_ADR.KU_NR
SET AUF_ADR.EMAIL = KUST_ADR.KU_EMAIL
WHERE AUF_ADR.ADR_ART = 2 
  AND KUST_ADR.KU_ADR_ART = 1 
  AND AUF_ADR.EMAIL != KUST_ADR.KU_EMAIL;

2. PostgreSQL Syntax #

PostgreSQL uses a FROM clause to include joined tables in the update. You can use either implicit or explicit joins:

Implicit Join Style (matches your original SELECT) #

UPDATE AUF_ADR
SET EMAIL = KUST_ADR.KU_EMAIL
FROM AUF_KOPF, AUF_STAT, KUST_ADR
WHERE AUF_ADR.AUF_NR = AUF_KOPF.AUF_NR
  AND AUF_ADR.AUF_NR = AUF_STAT.AUF_NR
  AND AUF_KOPF.KUNR = KUST_ADR.KU_NR
  AND AUF_ADR.ADR_ART = 2 
  AND KUST_ADR.KU_ADR_ART = 1 
  AND AUF_ADR.EMAIL != KUST_ADR.KU_EMAIL;

Explicit Join Style (more readable) #

UPDATE AUF_ADR
SET EMAIL = KUST_ADR.KU_EMAIL
FROM AUF_KOPF
JOIN AUF_STAT ON AUF_KOPF.AUF_NR = AUF_STAT.AUF_NR
JOIN KUST_ADR ON AUF_KOPF.KUNR = KUST_ADR.KU_NR
WHERE AUF_ADR.AUF_NR = AUF_KOPF.AUF_NR
  AND AUF_ADR.ADR_ART = 2 
  AND KUST_ADR.KU_ADR_ART = 1 
  AND AUF_ADR.EMAIL != KUST_ADR.KU_EMAIL;

3. SQL Server Syntax #

SQL Server requires explicitly referencing the target table in the FROM clause with joins:

UPDATE AUF_ADR
SET EMAIL = KUST_ADR.KU_EMAIL
FROM AUF_ADR
INNER JOIN AUF_KOPF ON AUF_ADR.AUF_NR = AUF_KOPF.AUF_NR
INNER JOIN AUF_STAT ON AUF_ADR.AUF_NR = AUF_STAT.AUF_NR
INNER JOIN KUST_ADR ON AUF_KOPF.KUNR = KUST_ADR.KU_NR
WHERE AUF_ADR.ADR_ART = 2 
  AND KUST_ADR.KU_ADR_ART = 1 
  AND AUF_ADR.EMAIL != KUST_ADR.KU_EMAIL;

4. Oracle Syntax #

Oracle uses a MERGE statement for updates involving joins (this is the most reliable approach):

MERGE INTO AUF_ADR target
USING (
  SELECT AUF_ADR.AUF_NR, KUST_ADR.KU_EMAIL
  FROM AUF_ADR
  JOIN AUF_KOPF ON AUF_ADR.AUF_NR = AUF_KOPF.AUF_NR
  JOIN AUF_STAT ON AUF_ADR.AUF_NR = AUF_STAT.AUF_NR
  JOIN KUST_ADR ON AUF_KOPF.KUNR = KUST_ADR.KU_NR
  WHERE AUF_ADR.ADR_ART = 2 
    AND KUST_ADR.KU_ADR_ART = 1 
    AND AUF_ADR.EMAIL != KUST_ADR.KU_EMAIL
) source
ON (target.AUF_NR = source.AUF_NR)
WHEN MATCHED THEN
  UPDATE SET target.EMAIL = source.KU_EMAIL;

Critical Tips Before Running: #

• Test first: Run your original SELECT query to confirm exactly which rows will be updated. For safety, add a limit (like LIMIT 10 in MySQL or FETCH FIRST 10 ROWS ONLY in PostgreSQL/Oracle) to test with a small subset.
• Backup data: Create a backup of the AUF_ADR table before executing the update to avoid accidental data loss.
• Handle NULLs: If KUST_ADR.KU_EMAIL can be NULL and you don’t want to overwrite valid emails with NULL, add AND KUST_ADR.KU_EMAIL IS NOT NULL to the WHERE clause.

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