Solved

Oracle Update one to many

Posted on 2012-03-29
12
656 Views
Last Modified: 2013-11-11
Hello all,

I have a table that holds all of my investigations for each individual. This table has a column called account_id. The account id is associated to one company. I noticed my previous worker update my case table with the wrong id. So I need to correct this. My investigation table as an invest_id and the account_id. My case table has multiple records for the account_id due to different users. I need to update the many records in my case table with the correct id from the investigation table. Please see sample data below for better detail.

invest table:
id       company     account_id
1        abc              12455

case table:
rpt_mth     user     case_id
2012-02     jane     12455
2012-02     john     12455
2012-02     mike    12455

I need the case table to read like this:
rpt_mth     user     case_id
2012-02     jane     1
2012-02     john     1
2012-02     mike    1

The case table has over 200k records over the last two years. So I need an update that will cross that period. I have tried the query below but am getting an error on updating many records from one.

update case_tbl
set case_id = invest_tbl.id
where case_tbl.case_id = invest_tbl.account_id;


Any help would be great.

Thanks,
Jeff
0
Comment
Question by:woodje
[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
  • 4
  • 3
  • 3
  • +1
12 Comments
 
LVL 6

Expert Comment

by:HomerTNachoCheese
ID: 37783146
update case_tbl
set case_id = 1
where case_tbl.case_id = 12455;

I think all you might have been doing was finding records with 12455 and re-entering 12455 again.  Or, you were ending up with no records to change.

All you need to do is look for 12455 and set it to 1 - no need to cross reference other tables' data I think.
0
 

Author Comment

by:woodje
ID: 37783194
HomerTNachoCheesem

Thank you for the quick response. However the data i provided is only a small sampling. I am dealing with updating over 200,000 records for server rpt_mth's and several different accounts I can't do them one at a time. Can you suggest a way to modify my query to pull the current account id from the case_tbl and pull the id from the investigation table and update my case_id in my case_tbl with the id from the invest_tbl.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37783223
Can you use a simple PL/SQL block?

drop table myinvest purge;
create table myinvest(id number, account_id number);

insert into myinvest values(1,12455);
insert into myinvest values(2,11111);
commit;

drop table mycase purge;
create table mycase(myuser varchar2(10), case_id number);

insert into mycase values('jane',12455);
insert into mycase values('john',12455);
insert into mycase values('mike',12455);
insert into mycase values('bill',11111);
commit;

begin
	for i in (select id,account_id from myinvest) loop
		update mycase set case_id=i.id where case_id=i.account_id;
	end loop;
	commit;
end;
/

Open in new window

0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 52

Accepted Solution

by:
Huseyin KAHRAMAN earned 500 total points
ID: 37783259
maybe this

UPDATE case_tbl c
   SET case_id =
          (SELECT id
             FROM invest i
            WHERE i.account_id = c.case_id)

Open in new window

0
 
LVL 6

Expert Comment

by:HomerTNachoCheese
ID: 37783292
I don't have Oracle installed anywhere to figure it out and get the syntax correct.

What you could try is create a select query that pulls all your data together for starters, where all fields you mentioned in your original post are present.

I am only running Access right now, so if I wrote it for Access SQL, it would look like something this:

UPDATE case_tbl
INNER JOIN invest_tbl ON case_tbl.case_id = invest_tbl.account_id
SET case_tbl.case_id = invest_tbl.id

Potential problem is that you cannot update the field that is joined.

Probably not the best way of doing things, but if you created a copy of the case_id field and all of its data into a field called case_id_old, then you could join on that field instead if you ran into that problem.
0
 

Author Comment

by:woodje
ID: 37783348
HainKurt,

Your idea looked great but I get the error More than one value was returned by a subquery.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37783423
Looks like you might have duplicate account ids in the invest table.

select account_id from invest group by account_id  having count(account_id) > 1;
0
 

Author Comment

by:woodje
ID: 37783486
slightwv,

Yes looks like I have about 16k of the 200k that do have dups for the account_id. If it is not one thing it is another.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37783498
My pl/sql loop will run but it will grap the last account_id it comes to which probably isn't what you want...

Once you get everything cleaned up:  Constraints, constraints, constraints...  Then you'll not get back into this mess.
0
 
LVL 52

Expert Comment

by:Huseyin KAHRAMAN
ID: 37784389
if you have multiple ids for case_id what is your solution?

invest table:
id       company     account_id
1        abc              12455
2        abc              12455

case table:
rpt_mth     user     case_id
2012-02     jane     12455
2012-02     john     12455
2012-02     mike    12455

???
0
 
LVL 52

Expert Comment

by:Huseyin KAHRAMAN
ID: 37784403
you can try one of these:

random pickup:

UPDATE case_tbl c
   SET case_id =
          (SELECT id
             FROM ((  SELECT id, ROWNUM rn
                        FROM invest i
                       WHERE i.account_id = c.case_id))
            WHERE rn = 1)

min id pickup

UPDATE case_tbl c
   SET case_id =
          (SELECT MIN (id)
             FROM invest i
            WHERE i.account_id = c.case_id)


max id pickup

UPDATE case_tbl c
   SET case_id =
          (SELECT MAX(id)
             FROM invest i
            WHERE i.account_id = c.case_id)
0
 

Author Closing Comment

by:woodje
ID: 37787444
This worked with some modification to use max invest_id and filtering on dates. But your sample pointed me in the right direction to get the job done. Thanks.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
"Disruption" is the most feared word for C-level executives these days. They agonize over their industry being disturbed by another player - most likely by startups.
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.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

730 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