Solved

Oracle Update one to many

Posted on 2012-03-29
12
653 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
  • 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 76

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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
LVL 51

Accepted Solution

by:
HainKurt 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 76

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 76

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 51

Expert Comment

by:HainKurt
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 51

Expert Comment

by:HainKurt
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Note: You must have administrative privileges in order to create/edit Roles. Salesforce.com (http://www.salesforce.com/) (SFDC) is a cloud-based customer relationship management (CRM) system. It is a database most commonly used by sales and marke…
Get to know the ins and outs of building a web-based ERP system for your enterprise. Development timeline, technology, and costs outlined.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to take different types of Oracle backups using RMAN.

792 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