Link to home
Start Free TrialLog in
Avatar of PRAVEEN T
PRAVEEN TFlag for United States of America

asked on

Need help on writing the SQL to update the table column based on provided sprad sheet (One-time job)

Hi Experts,

I am trying to write the oracle sql to update a table column based on the spread sheet ..

We have the spread sheet like this values

BatchID     Queue     comment
1                KTM          Test1
2                KTM2         Test2
3                KTM3          Test3

we want to use the above spread sheet (That is the output of a SQL) --and update the table column
This is the one-time job in my current requirement., Please help me how to achieve?

Let me know if you have any questions...

Regards,
Kumar
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

I would save the spreadsheet as a CSV, load it into a junk table and do a MERGE statement.
If the spreadsheet is the result of a sql query, why do you need to use a spreadsheet at all?
Is someone altering the data in that spreadsheet?

Which columns are you changing? (that isn't clear) e.g.
     are you "matching" on batchid only, and then updating Queue  &comment
OR
     are you "matching" via batchid and queue then changing just the comment column?
It is not clear to us which column (or columns?) of a table you want to update based on the sample data you provided.  Or do you actually need to insert this data into a table somewhere?

Then, when you tell us that this sample data is "the output of a SQL [query]" it makes us wonder what value the spreadsheet adds?  If this data is actually in a table already, why do you need to update (or insert?) this data from a spreadsheet into a different table?  Could you create a view on the existing data instead?  Or, if you do need to do an update, could you do that directly from the existing table and not use the spreadsheet at all?

We don't know enough now about what your "one-time job" requires, or where the data comes from, or where it will be used to give you a good answer.  If you can give us some more information, we should be able to give you some good suggestions.
Use the same query which you have used to extract the data into spread sheet as correlated sub-query and update the target table.

option 2: Prepare the update statement in spreed sheet by using formula and concatenating symbol in spread sheet and run the generated update statements against to the target table.

example1:    =" update <tablename> set column1="&A1&";"
example2:    =" update <tablename> set column1='"&A1&"';"
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.