Solved

enter several value pairs into a oracle sql table using scripts

Posted on 2013-12-12
9
459 Views
Last Modified: 2013-12-13
Hi!

I have several value pairs and want to use one value to find the correct row and then insert the other value into one of the columns in a table:

FOR ALL VALUES IN (LIST OF PAIRS)

UPDATE TABLE1
   SET COL2=VAR2
 WHERE COL1=VAR1

NEXT

Open in new window

Can someone please help me with this? Its probably very simple, but I don't know how to do it and I am in a bit of a rush... ;-)

Thx!
IVer
0
Comment
Question by:IverErling
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39713901
Please provide sample data and expected results.
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39713955
As slightwv suggested, more information would be great ;-)
In the meantime you'd probably take a look at the MERGE statement:

MERGE INTO table1 e
    USING (SELECT key, value FROM your_list_of_pairs_table) h
    ON (e.some_id = h.some_id)
  WHEN MATCHED THEN
    UPDATE SET e.col2 = h.var2, e.col1 = h.var1;

Open in new window


You'd be able to change this (not testet) snippet to suite your needs...
0
 

Author Comment

by:IverErling
ID: 39713957
The value pairs can look like this:
ReqID  	Email
-----  	-----
43292	addr@company.com
39840	man@comp.co.uk
91823	woman@firm.no

etc.

Open in new window


and I don't expect any results other than that table1 is updated with the email addresses in column2 for all matching ReqIDs found in column1

so a table that looks like this before the update

ReqID  	Email			Value3
-----  	-----			-------
43292				some
91283				thing
39840				in
91823				this
93821				column

Open in new window

looks like this after the update:
ReqID  	Email			Value3
-----  	-----			-------
43292	addr@company.com	some
91283				thing
39840	man@comp.co.uk		in
91823	woman@firm.no		this
93821				column

Open in new window


etc.

IVer
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 32

Expert Comment

by:awking00
ID: 39713997
Then Alex140181's merge should do the trick -
merge into table1 t
using valuepairs v
on (t.reqid = t.reqid)
when matched then
update set t.email = v.email;
0
 
LVL 32

Expert Comment

by:awking00
ID: 39714000
sorry for the typo, meant to say on (t.reqid = v.reqid)
0
 

Author Comment

by:IverErling
ID: 39714078
Ok, sorry for being so ignorant, but the statment "using valuepairs v", how do I get my values into "valuepairs"?  Is "valuepairs" a table? I am currently sitting here with all the value pairs in an Excel sheet and can copy them into a text file or as part of a script or I guess I could try to make a provisional table of them, but I'm not sure I have the rights to create new tables...

Iver
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 450 total points
ID: 39714095
For  1-time load, just take the values from Excel and copy/paste them into a script like:
Strings are wrapped with single quotes.  Numbers can stand alone without them.

select '43292','addr@company.com' from dual
union all
select '39840','man@comp.co.uk' from dual
union all
...


Then once you have the entire list in a select, paste it into the above MERGE statement

...
USING (<<The above UNION ALL script>>) h
...
0
 
LVL 13

Assisted Solution

by:Alexander Eßer [Alex140181]
Alexander Eßer [Alex140181] earned 50 total points
ID: 39714128
I agree with slightwv regarding the "first shot". In the end, you could automate the whole process by exporting/saving the Excel data into a CSV file and then using the SQL Loader to import into some kind of staging table, which in turn is being processed by a DBMS/Scheduler job... (just a scenario)...
If you need further help on this, let me/us know ;-)
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 450 total points
ID: 39714145
You know, now that I've actually thought about this for a minute, we are making this harder than it needs to be.

Just take your values from excel and generate individual update statements:
update table1 set email='addr@company.com' where reqid=43292;
...

Save the results in a .sql file and execute it from sqlplus.

If you saved it as c:\q.sql then: sqlplus username/password @c:\q.sql
0

Featured Post

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to recover a database from a user managed backup

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question