Solved

enter several value pairs into a oracle sql table using scripts

Posted on 2013-12-12
9
449 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
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 76

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 76

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 76

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
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…

821 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