Using "WITH" and "UPDATE" statements in the same SQL query

Question

I have a table that I need to update with some data from an Excel spreadsheet. I am thinking of a query along these lines:

WITH temp AS(
(SELECT 'abcd' AS oldvalue, 'defg' AS newvalue FROM dual) UNION
(SELECT .....) --About 300 lines of this, copied from Excel and then formatted into the SELECT statement
)
UPDATE mytable
   SET name = (SELECT newvalue FROM temp WHERE mytable.name = temp.oldvalue)

But Oracle doesn't seem to like having a "WITH" and "UPDATE" statement in the same query. I get an error saying "missing SELECT keyword". I have found out that I can put the temp table definition within the SELECT statement, i.e.

 SET name = (SELECT newvalue FROM (
         (SELECT 'abcd' AS oldvalue, 'defg' AS  newvalue FROM dual) UNION
         (SELECT .....)
          ) temp WHERE mytable.name = temp.oldvalue)

But that is horribly, horribly messy code to define a table like that right in the middle of the query. I cringe just thinking about it. There has to be a better way to do this. Should I set up a global temporary table? Or am I just missing some simple syntax that would make this work the original way?

Answer 1

You can use a with clause in an update; you just have to do it in the right place:

UPDATE mytable
   SET name = (WITH temp AS((SELECT 'abcd' AS oldvalue, 'defg' AS newvalue FROM dual) UNION
                            (SELECT .....) --About 300 lines of this, copied from Excel and then formatted into the SELECT statement
                           )
               SELECT newvalue
               FROM   temp
               WHERE  mytable.name = temp.oldvalue);

However, you're probably wanting to only update rows that exist in the temp subquery, so you would need an additional where clause:

UPDATE mytable
   SET name = (WITH temp AS((SELECT 'abcd' AS oldvalue, 'defg' AS newvalue FROM dual) UNION
                            (SELECT .....) --About 300 lines of this, copied from Excel and then formatted into the SELECT statement
                           )
               SELECT newvalue
               FROM   temp
               WHERE  mytable.name = temp.oldvalue)
WHERE  EXISTS (WITH temp AS((SELECT 'abcd' AS oldvalue, 'defg' AS newvalue FROM dual) UNION
                            (SELECT .....) --About 300 lines of this, copied from Excel and then formatted into the SELECT statement
                           )
               SELECT NULL
               FROM   temp
               WHERE  mytable.name = temp.oldvalue);

Alternatively, use a MERGE statement:

merge into mytable tgt
  using (WITH temp AS((SELECT 'abcd' AS oldvalue, 'defg' AS newvalue FROM dual) UNION
                      (SELECT .....) --About 300 lines of this, copied from Excel and then formatted into the SELECT statement
                     )
         SELECT mytable.rowid r_id,
                temp.newvalue
         FROM   temp
         inner  join mytable on mytable.name = temp.oldvalue) src
    on (tgt.rowid = src.r_id)
when matched then
update set tgt.name = src.newvalue;

N.B. you have to join to the actual table in the source query of the merge statement because you're trying to update the column that's being joined on, which you can't do in a merge statement - hence I've switched the merge join to join on mytable.rowid.

You'd have to test both statements to see which one is most performant on yor data.

Answer 2

Try this:

UPDATE mytable m
   SET name =
          (WITH temp
                AS (SELECT 'abcd' AS oldvalue, 'defg' AS newvalue FROM DUAL
                    UNION ALL /* Use UNION ALL instead of UNION in this case */
                    SELECT 'efgh' AS oldvalue, 'klmn' AS newvalue FROM DUAL)
           SELECT newvalue
             FROM temp
            WHERE temp.oldvalue = m.name)

Or, you can ust generate script by inserting this into adjacent cell in Excel:

   ="UPDATE YOUR_TABLE_NAME SET NAME="&CELL_WITH_NEW_VALUE&" WHERE NAME="&CELL_WITH_OLD_VALUE&"; COMMIT;"

and drag down this up to the end of value list.

After, run this as script and you're done!