Update multiple values with where exists clause

Question

I have this problem with PL/SQL. I have 2 tables with same columnus. One is for Current Run, the other for history.

At every run, I need to update history with just values that I found in current run.

Current Table

|   ID  |__NAME__|__SURNAME__|__CITY__|
| 10000 |  ABC   |   CDE     |   IT   |
| 10001 |  EFG   |   ASD     |   EN   |
| 10005 |        |           |        |

History Table

|   ID  |__NAME__|__SURNAME__|__CITY__|
| 10000 |  FFF   |    AAA    |   EN   |
| 10001 |  DDD   |    BBB    |   GR   |
| 10005 |  JKO   |    POI    |   GR   |
| 10006 |  DLK   |    MIN    |   IT   |

As you can see, Current Table has a record with all empty values except ID=10005. So, I need to update history (name, surname, city) from current table where id exists in current table and name, surname and city are not null.

I've tried with

UPDATE HISTORY
    SET (NAME, SURNAME, CITY) = (
        SELECT NAME, SURNAME, CITY
        FROM CURRENT temp
        WHERE temp.name is not null and temp.id = history.id
        )
    WHERE exists (
        SELECT NAME, SURNAME, CITY
        FROM CURRENT temp
        WHERE temp.name is not null and temp.id = history.id
    );

But it doesn't work. I need to underline that when name is null in current table, then surname and city are also null.

Are you using MySQL or Postgres? – Tim Biegeleisen Jun 30 '16 at 14:13 — Tim Biegeleisen, Jun 30 '16 at 14:13
PLSQL- I'm going to edit my question – Tizianoreica Jun 30 '16 at 14:13 — Tizianoreica, Jun 30 '16 at 14:13

Mottor · Accepted Answer · 2016-07-01T07:44:56.180

Actually the only wrong thing in your query is the name of the table CURRENT. It is a reserved word in oracle and is not recommended to use it as table name. You have to use "" this to work. Here:

UPDATE HISTORY
    SET (NAME, SURNAME, CITY) = (
        SELECT NAME, SURNAME, CITY
        FROM "CURRENT" temp
        WHERE temp.name is not null and temp.id = history.id
        )
    WHERE exists (
        SELECT NAME, SURNAME, CITY
        FROM "CURRENT" temp
        WHERE temp.name is not null and temp.id = history.id
    );

You can use MERGE too (See the explain plan for both):

MERGE INTO HISTORY_TABLE a
     USING (SELECT ID, NAME, SURNAME, CITY
              FROM CURRENT_TABLE
             WHERE NAME IS NOT NULL) b
        ON (a.id = b.id)
WHEN MATCHED THEN
   UPDATE SET a.NAME = b.NAME, a.SURNAME = b.SURNAME, a.CITY = b.CITY

Update multiple values with where exists clause

1 Answers1