Solved

Oracle Update one to many

Posted on 2012-03-29
12
647 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
 
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
For cloud, the “train has left the station” and in the Microsoft ERP & CRM world, that means the next generation of enterprise software from Microsoft is here: Dynamics 365 is Microsoft’s new integrated business solution that unifies CRM and ERP fun…
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 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.

760 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now