Creating an update script from Oracle using TOAD

Question

In Oracle, using TOAD, we are able to create an insert script from a table.

However, I need to create an UPDATE script based on the values of each row

where the CLIENTID is 'blah blah'

Originally, what I did was create an Excel spreadsheet with all the necessary data and for each row, I pasted a

UPDATE TABLE BLAH BLAH SET COLUMN1 = "xxx", COLUMN2 = '' where CLIENT_ID = "123'

..but I ran into some difficulties with exporting the spreadsheet into a text based DOS file and converting it to a .sql as so to edit but it was messy and unpredictable.

Has anyone have a better idea I can implement?

Answer 1

I haven't seen any feature in Toad that helps write update queries in this fashion. But loading data directly is something that Toad makes very easy.

May be you could do this.

create table tmp_excel_data
( column1 varchar2(100),
  column2 varchar2(100),
  client_id varchar2(100)
);

Use toad to directly load data into this table. The update is simple from this point.

update CLIENT_DATA tgt
  tgt.column1 = (select column1
                  from tmp_excel_data src
                  where tgt.client_id = src.client_id);

--dont forget to commit.

This will also let you cleanse your data (removing dups..for example) if needed.