Home » SQL & PL/SQL » SQL & PL/SQL » Updating multiple records
Updating multiple records [message #288408] Mon, 17 December 2007 07:55 Go to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi,
I have to populate data in an empty table. First I have to populate some columns and after that I have to update the rest columns. I am getting the data from more than one table to pupulate the table.
I have created a Cursor:
CURSOR c_fgsr IS
    SELECT DISTINCT SUM(b.qty/1000) inv_qty, 
           a.division division, a.sopcust sopcust, a.program program,
           a.sopgrp sopgrp, a.sopclothtype sopclothtype,
           a.color_group color_group, a.sized sized, a.sopmfgtype sopmfgtype,
           a.product_type product_type, b.scheddate,
           DECODE(COUNT(a.skutype),1,a.skutype,'****') skutype,
           DECODE(a.sopmfgtype,'MAKE','NA','SOURCED') mfgloc
      FROM springs.si_sop_fgitem a,
           springs.si_sop_fgsr b
     WHERE a.item = b.item
     GROUP BY b.item, a.division, a.sopcust, a.program, a.sopgrp,
           a.sopclothtype, a.color_group, a.sized, a.sopmfgtype,
           a.product_type, b.scheddate, skutype

After that I am inserting records to the table as :-

FOR v_sr IN c_fgsr
    LOOP
      INSERT INTO springs.si_sop_aggr_fgsr
             (sbu, customer, program, sopgrp, cloth_type, 
              fin_type, fin_size, mfg_type, prod_type, 
              fg_type, mfg_loc, Mfg_fac)
      VALUES (v_sr.division, v_sr.sopcust, v_sr.program, 
              v_sr.sopgrp, v_sr.sopclothtype,  v_sr.color_group,
              v_sr.sized, v_sr.sopmfgtype, v_sr.product_type,
              v_sr.skutype, v_sr.mfgloc, NULL);
       
     n_ins_inv_cnt := n_ins_inv_cnt + 1;
       
  END LOOP;

This Insert statement inserts 100 records.
One column named Mfg_fac is inserted with NULL value.
Later I have to update this column mfg_fac with value fetched from tables springs.si_sop_fgitem and stsc.loc as below.

UPDATE springs.si_sop_aggr_fgsales
     SET mfg_fac =
           (SELECT DISTINCT  a.si_prim_vendor 
               FROM springs.si_sop_fgitem a,
                    stsc.loc b
            WHERE a.loc = b.si_prim_vendor


But since the select statement is returning multiple records, I am not able to update the value of mfg_fac.

Please suggest me as how to update multiple records in this kind of scenario.
Give me a hint ashow to accomplish this.

Thanks and Regards,
Mona
Re: Updating multiple records [message #288409 is a reply to message #288408] Mon, 17 December 2007 08:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

But since the select statement is returning multiple records, I am not able to update the value of mfg_fac.

Please suggest me as how to update multiple records in this kind of scenario.

This 2 sentences have no relation.
The first one means you want to update a field of ONE row with several values which is impossible.

You can do the whole stuff in one SQL insert statement (assuming "First I have to populate some columns and after that I have to update the rest columns" is true and one of the "springs.si_sop_aggr_fgsr" or "springs.si_sop_aggr_fgsales" is wrong and should be the same).

Regards
Michel

[Updated on: Mon, 17 December 2007 08:10]

Report message to a moderator

Re: Updating multiple records [message #288410 is a reply to message #288409] Mon, 17 December 2007 08:12 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

After inserting 100 records to the table, I have to update the column mfg_fac which is now having NULL values. I have to update the records one by one.
Please suggest me a way to update the 100 records with value I am getting from the select statement used in UPDATE statement.
My update statement is wrong here as it is trying to update one column with multiple values at a time. A loop or something is needed to accomplish this.

I do not have much experience in Oacle, please help me on this.

Thanks
Mona
Re: Updating multiple records [message #288413 is a reply to message #288408] Mon, 17 December 2007 08:15 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

What is the relation between the tables
springs.si_sop_aggr_fgsales and springs.si_sop_fgitem/stsc.loc??

See the difference:

 update tab
 set col = (select 1 from dual;)
 
 update tab 
 set col = (select 1,2,3 from dual);


Edit[Establish a relation between springs.si_sop_aggr_fgsales and springs.si_sop_fgitem/stsc.loc]

Kiran.

[Updated on: Mon, 17 December 2007 08:19]

Report message to a moderator

Re: Updating multiple records [message #288414 is a reply to message #288413] Mon, 17 December 2007 08:22 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

I have to update the clumn mfg_fac of the table springs.si_sop_aggr_fgsales fom data in tables springs.si_sop_fgitem/stsc.loc??
Re: Updating multiple records [message #288416 is a reply to message #288413] Mon, 17 December 2007 08:23 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member



Edit[Establish a relation between springs.si_sop_aggr_fgsales and springs.si_sop_fgitem/stsc.loc]

Re: Updating multiple records [message #288418 is a reply to message #288408] Mon, 17 December 2007 08:30 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Quote:

Edit[Establish a relation between springs.si_sop_aggr_fgsales and springs.si_sop_fgitem/stsc.loc]


Analysis:

UPDATE tab a
   SET a.col = (SELECT col
                FROM tab1 b
               WHERE b.col1 = a.col1)


Kiran.
Re: Updating multiple records [message #288420 is a reply to message #288410] Mon, 17 December 2007 08:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

. A loop or something is needed to accomplish this.

No SQL is there to make update on a set.
You just have to precisely specify the relation between the source row and the target row and express it in SQL.

I still maintain you can do all this (from insert to update) in just one SQL statement.

Regards
Michel
Re: Updating multiple records [message #288422 is a reply to message #288418] Mon, 17 December 2007 08:40 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

I only need a hint as how to update one column in multiple rows using a loop or something.
Please do not go over the tables but only assume that here the inner SELECT clause is returning me 100 values for the column mfg_fac. I have to use the values to update all the 100 records for the column mfg_fac.
I only want to know as how to update multiple records at a time in a loop or something.

UPDATE springs.si_sop_aggr_fgsales
SET mfg_fac =
(SELECT DISTINCT a.si_prim_vendor
FROM springs.si_sop_fgitem a,
stsc.loc b
WHERE a.loc = b.si_prim_vendor
Re: Updating multiple records [message #288429 is a reply to message #288422] Mon, 17 December 2007 08:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you see Kiran example?

Regards
Michel
Re: Updating multiple records [message #288450 is a reply to message #288408] Mon, 17 December 2007 10:27 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi Michel,
M problem is that while executing the update statement I am getting the error of 'returning multiple record' that is the inner select statement could return mltiple records. Please help me solve this as how to update a column when the select statement returns multiple rows.
UPDATE tab a
   SET a.col = (SELECT col
                FROM tab1 b
               WHERE b.col1 = a.col1)
Re: Updating multiple records [message #288453 is a reply to message #288450] Mon, 17 December 2007 10:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
That what we are trying to say.
Only you know the relation between the inner and the outer query.
Only you know the data.
Only you know how to specify the inner query so that it must return only one row per row of the outer query.

Your inner query does not make any reference to the outer row, so it is not surprising you get multiple rows.
See Kiran example, his inner query refers the outer row through "a.col1".

Regards
Michel

[Updated on: Mon, 17 December 2007 10:35]

Report message to a moderator

Re: Updating multiple records [message #288495 is a reply to message #288453] Mon, 17 December 2007 15:16 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi Michel,
I am giving another example:-
update application_xml_q2007 a
set a.xml_name = (select b.xml_name from application_xml_info_add b where a.form_id = b.form_id);

Here the subquery is returning multiple records. Suppose the table has 100 records and inner query returns 100 different values....how should I update the table.
Please help me on this...
for record in cursor loop  update table    set column - record.value  where key = record.key;end loop;
Re: Updating multiple records [message #288498 is a reply to message #288495] Mon, 17 December 2007 15:32 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
monasingh wrote on Mon, 17 December 2007 16:16

Hi
update application_xml_q2007 a
set a.xml_name = (select b.xml_name from application_xml_info_add b where a.form_id = b.form_id);

Here the subquery is returning multiple records. Suppose the table has 100 records and inner query returns 100 different values....how should I update the table.



But if the inner query is returning multiple rows, how do YOU know which is the value that you want to update application_xml_q2007 with? That is what people are trying to ask you.

[edit - added an simplistic example ]

Example:
Table 1

col1 col2
---- ----
A    NULL
B    NULL
C    NULL

Table 2

cola colb
---- ----
A    1
A    2
A    3


Which value of A in table 2 do you want to update row A in table 1? Or are you just not asking your question properly? Why not give sample table scripts and data to make life easier for everyone. It is in Sticky at the top of the forum on how to get meaningful answers to questions.

[unrelated note - I have returned unharmed from 11,000 feet latitude 40.57, longitude -111.61]

[Updated on: Mon, 17 December 2007 16:00]

Report message to a moderator

Re: Updating multiple records [message #288529 is a reply to message #288408] Mon, 17 December 2007 23:24 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Hi Mona,

Check my first post, there i gave you below example.

Quote:

See the difference:

case1:
 update tab
 set col = (select 1 from dual;)
 
case:2
 update tab 
 set col = (select 1,2,3 from dual);



Your query is like case:2, which returns multiple row(like 1,2,3 in my example). How do we know which value you want to update with? is it 1 or 2 or 3?

If you don't bother of what value you getting from inner query to update, then use rownum = 1 in inner query so that you will get one row at each time.

But that may not be your data which you want to update. It is just can avoid to get the multiple records.

Hope you understand now.

Regards,
Kiran.

[Updated on: Mon, 17 December 2007 23:26]

Report message to a moderator

Re: Updating multiple records [message #288534 is a reply to message #288408] Mon, 17 December 2007 23:41 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
[unrelated note - I have returned unharmed from 11,000 feet latitude 40.57, longitude -111.61]

Alta is nice. I prefer Heavenly a bit west of UT.
I've spent the night at 14,495 & was the 1st in CA the next morning to see the sunrise.
Re: Updating multiple records [message #288542 is a reply to message #288534] Mon, 17 December 2007 23:57 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
anacedent wrote on Tue, 18 December 2007 06:41

[unrelated note - I have returned unharmed from 11,000 feet latitude 40.57, longitude -111.61]

Alta is nice. I prefer Heavenly a bit west of UT.
I've spent the night at 14,495 & was the 1st in CA the next morning to see the sunrise.

Good to hear you're back in one piece. Welcome back to ground-level Ana! Wink
Re: Updating multiple records [message #288739 is a reply to message #288542] Tue, 18 December 2007 08:11 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Frank wrote on Tue, 18 December 2007 00:57

anacedent wrote on Tue, 18 December 2007 06:41

[unrelated note - I have returned unharmed from 11,000 feet latitude 40.57, longitude -111.61]

Alta is nice. I prefer Heavenly a bit west of UT.
I've spent the night at 14,495 & was the 1st in CA the next morning to see the sunrise.

Good to hear you're back in one piece. Welcome back to ground-level Ana! Wink


Actually, it was I who have returned.

In the realm of Steve Wright's lines "Everything is within walking distance if you have the time" and "It doesn't make a difference what temperature a room is, it's always room temperature" I would have to say that wherever you are, it's ground level Wink
Re: Updating multiple records [message #288789 is a reply to message #288739] Tue, 18 December 2007 12:51 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Thanks to all of you. Finally I have understood the problem and have solved the query by adding some more condition to WHERE clause.

Mona
Re: Updating multiple records [message #288797 is a reply to message #288739] Tue, 18 December 2007 14:08 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Sorry for the mixup..
joy_division wrote on Tue, 18 December 2007 15:11


In the realm of Steve Wright's lines "Everything is within walking distance if you have the time" and "It doesn't make a difference what temperature a room is, it's always room temperature" I would have to say that wherever you are, it's ground level Wink

Ah, just as "Where are you?" "I'm here. I'm ALWAYS here."
Previous Topic: Trim query
Next Topic: diffence between procedure and function.
Goto Forum:
  


Current Time: Sun Dec 04 12:56:20 CST 2016

Total time taken to generate the page: 0.09303 seconds