Changing SQL SELECT to UPDATE

Question

I don't regularly use SQL UPDATE and I have a complete brain block on how I re-write this SELECT to update instead.

Basically I want to replace AUF_ADR.EMAIL with the result from KUST_ADR.KU_EMAIL.

SELECT AUF_KOPF.AUF_NR, 
       AUF_ADR.EMAIL, 
       KUST_ADR.KU_EMAIL 
FROM   AUF_ADR, 
       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
       AND (SELECT SUM(AUF_STAT.RG_ANZ) 
            FROM   AUF_STAT 
            WHERE  AUF_STAT.AUF_NR = AUF_KOPF.AUF_NR) = 0;

I understand I need to start with something like

UPDATE AUF_ADR
SET    AUF_ADR.EMAIL = KUST_ADR.KU_EMAIL

but this is where I don't understand what happens next i.e. I don't use a FROM in an UPDATE and what about the WHERE?

you can refer to this topic [link](https://stackoverflow.com/questions/1293330/how-can-i-do-an-update-statement-with-join-in-sql) — Luca, Apr 24 '18 at 08:34
Use the ANSI `join` syntax and not implicit a,b,c,d for joins. Then convert your query into a `MERGE INTO` statement. — Kaushik Nayak, Apr 24 '18 at 14:31
Please provide some simplified DDL code INCLUDING constraints, so that it can be seen how the tables are "connected". I have written an example - see https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=e02c2e42fc7419e268918324111b214e — stefan, Apr 26 '18 at 06:03

score 0 · Answer 1 · answered Apr 25 '18 at 06:01

Something like this, perhaps? I just rewrote your code; EXISTS is here to prevent setting non-matching email addresses to NULL.

UPDATE auf_adr
   SET email =
          (SELECT 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
                  AND (SELECT SUM (auf_stat.rg_anz)
                         FROM auf_stat
                        WHERE auf_stat.auf_nr = auf_kopf.auf_nr) = 0)
 WHERE EXISTS
          (SELECT NULL
             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
                  AND (SELECT SUM (auf_stat.rg_anz)
                         FROM auf_stat
                        WHERE auf_stat.auf_nr = auf_kopf.auf_nr) = 0);

It is up to you; I don't know what the SELECT you wrote returns - obviously, more than a single value. DISTINCT might help, but - on the other hand - it might not. If not, you'll have to further restrict the WHERE clause so that it really returns only one value. — Littlefoot, Apr 25 '18 at 08:55
Is there a quick way to work out which column is causing the additional rows? Large tables, large database! — user6888062, Apr 27 '18 at 18:05

Changing SQL SELECT to UPDATE

1 Answers1