[Last Call] Learn how to a build a cloud-first strategyRegister Now

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

Oracle Update one to many

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
woodje
Asked:
woodje
  • 4
  • 3
  • 3
  • +1
1 Solution
 
HomerTNachoCheeseCommented:
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
 
woodjeAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
HainKurtSr. System AnalystCommented:
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
 
HomerTNachoCheeseCommented:
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
 
woodjeAuthor Commented:
HainKurt,

Your idea looked great but I get the error More than one value was returned by a subquery.
0
 
slightwv (䄆 Netminder) Commented:
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
 
woodjeAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
HainKurtSr. System AnalystCommented:
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
 
HainKurtSr. System AnalystCommented:
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
 
woodjeAuthor Commented:
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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