Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

enter several value pairs into a oracle sql table using scripts

Posted on 2013-12-12
9
Medium Priority
?
480 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 14

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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 1800 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 14

Assisted Solution

by:Alexander Eßer [Alex140181]
Alexander Eßer [Alex140181] earned 200 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 1800 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

721 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