Using “WITH” and “UPDATE” statements in the same SQL query

Home / Uncategorized / 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:
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!
Read more

Leave a Reply

Your email address will not be published. Required fields are marked *