Search code examples
sqloraclesql-update

Using Alias in WHERE clause for SELECT in UPDATE

I need to update a couple of hundreds lines in a table with data from other tables. The code works, but is there a possibility to not change the id and key and use id IN ('56454', '56546'...)

UPDATE kontakt ko
SET  ko.Strasse1 = (SELECT old FROM audit WHERE key = '962881' AND cname = 'STRASSE1' AND old IS NOT NULL)
    ,ko.plz = (SELECT old FROM audit WHERE key = '962881' AND cname = 'PLZ' AND old IS NOT NULL)
    ,ko.ort = (SELECT old FROM audit WHERE key = '962881' AND cname = 'ORT' AND old IS NOT NULL)
WHERE id = 962881;

COMMIT;

I tried using AS or replacing names, but without success. I couldn't find an answer.

Solution

  • You want to make the subqueries correlated, such that they refer to the updating row's ID:

    UPDATE kontakt ko
SET  ko.Strasse1 = (SELECT old FROM audit WHERE key = TO_CHAR(ko.id) AND cname = 'STRASSE1' AND old IS NOT NULL)
    ,ko.plz      = (SELECT old FROM audit WHERE key = TO_CHAR(ko.id) AND cname = 'PLZ' AND old IS NOT NULL)
    ,ko.ort      = (SELECT old FROM audit WHERE key = TO_CHAR(ko.id) AND cname = 'ORT' AND old IS NOT NULL)
WHERE id IN (56454, 56546, ...);

COMMIT;

    Your statement (and mine accordingly) looks a bit off, though. The subqueries must return one value each (or no row, which would be considered null then). It seems hence that you expect the table audit to contain multiple values per key and cname of which only one old value is not null. Is this really guaranteed to be the case? Or are you trying to achieve something else?

    Maybe key and cname are unique in the audit table and you really want to keep the original value in case the audit value is null (or does not exist). That would require COALESCE (or NVL):

    UPDATE kontakt ko
SET  ko.strasse1 = COALESCE((SELECT old FROM audit WHERE key = TO_CHAR(ko.id) AND cname = 'STRASSE1'), ko.strasse1)
    ,ko.plz      = COALESCE((SELECT old FROM audit WHERE key = TO_CHAR(ko.id) AND cname = 'PLZ'), ko.plz)
    ,ko.ort      = COALESCE((SELECT old FROM audit WHERE key = TO_CHAR(ko.id) AND cname = 'ORT'), ko.ort)
WHERE id IN (56454, 56546, ...);

COMMIT;