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

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

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 = 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 = 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?

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 =

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 *