Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 489
  • Last Modified:

enter several value pairs into a oracle sql table using scripts

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
Iver Erling Arva
Asked:
Iver Erling Arva
  • 3
  • 2
  • 2
  • +1
3 Solutions
 
slightwv (䄆 Netminder) Commented:
Please provide sample data and expected results.
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
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
 
Iver Erling ArvaSenior consultantAuthor Commented:
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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
awking00Commented:
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
 
awking00Commented:
sorry for the typo, meant to say on (t.reqid = v.reqid)
0
 
Iver Erling ArvaSenior consultantAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
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
 
slightwv (䄆 Netminder) Commented:
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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now