Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

enter several value pairs into a oracle sql table using scripts

Posted on 2013-12-12
9
Medium Priority
?
488 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:Iver Erling Arva
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 78

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:Iver Erling Arva
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:Iver Erling Arva
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 78

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 78

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

Independent Software Vendors: 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!

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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

916 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