OraFAQ Forum: SQL & PL/SQL » Updating missing values

Home » SQL & PL/SQL » SQL & PL/SQL » Updating missing values (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Updating missing values [message #628499]

Tue, 25 November 2014 12:35

ramya_162
Messages: 107
Registered: August 2013
Location: Banglore

Senior Member

Hi Experts,

I have data in a table as below.

OMERS_NO    UNITS_NO   BATC_NO  SERIES_NO   PRODUCTION_START_DT  PRODUCTION_END_DT
22338855	1	1	ASMNDSC	     06/Oct/14	          06/Oct/15
22338855	1	2			
22338855	4	1	AQDFHNV	     07/Oct/14	          07/Oct/15
22338855	4	2			
22338855	4	3			
22338855	7	2			
22338855	7	3			
22338855	7	4			
22338855	10	1	HKMNBTE	    13/Oct/14	          13/Oct/15
22338855	11	1	HKMNBTE	    13/Oct/14	          13/Oct/15
44668899	1	1	ABND3U9	    15/Nov/14	          15/Nov/16
44668899	1	2		    15/Nov/14	          15/Nov/16
44668899	2	1	ABND3U9	    15/Nov/14	          15/Nov/16
44668899	2	2		    15/Nov/14	          15/Nov/16
66778899	12	1	AQSFCMF	    13-Oct-14	          13/Oct/15
66778899	12	2	AQSFCMF	    13/Oct/14	          13/Oct/15

For update I have to consider the records for each OMRS_NO and UNITS_NO morethan one record and with miminum one SERIES_NO is NULL.

In the above data we no need to consider these records for update since at least no one SERIES_NO is NULL.

66778899 12 1 AQSFCMF 13-Oct-14 13/Oct/15
66778899 12 2 AQSFCMF 13/Oct/14 13/Oct/15

I want to update the SERIES_NO,PRODUCTION_START_DT and PRODUCTION_END_DT which are missing for child records
with the parent value in BATCH_QUOTA table.

After update the result would be.

OMERS_NO    UNITS_NO   BATC_NO  SERIES_NO   PRODUCTION_START_DT  PRODUCTION_END_DT
22338855	1	1	ASMNDSC	     06/Oct/14	          06/Oct/15
22338855	1	2	ASMNDSC      06/Oct/14	          06/Oct/15
22338855	4	1	AQDFHNV	     07/Oct/14	          07/Oct/15
22338855	4	2	AQDFHNV	     07/Oct/14	          07/Oct/15
22338855	4	3	AQDFHNV	     07/Oct/14	          07/Oct/15
22338855	7	2	SDFGO	     07/Oct/14	          07/Oct/15
22338855	7	3	SDFGO	     07/Oct/14	          07/Oct/15
22338855	7	4	SDFGO        07/Oct/14	          07/Oct/15
22338855	10	1	HKMNBTE	    13/Oct/14	          13/Oct/15
22338855	11	1	HKMNBTE	    13/Oct/14	          13/Oct/15
44668899	1	1	ABND3U9	    15/Nov/14	          15/Nov/16
44668899	1	2	ABND3U9	    15/Nov/14	          15/Nov/16
44668899	2	1	ABND3U9	    15/Nov/14	          15/Nov/16
44668899	2	2	ABND3U9	    15/Nov/14	          15/Nov/16
66778899	12	1	AQSFCMF	    13-Oct-14	          13/Oct/15
66778899	12	2	AQSFCMF	    13/Oct/14	          13/Oct/15

I am providing tables and test data.

CREATE TABLE PODUCT_MANFACUTERS
(
  OMERS_NO             NUMBER(25)               NOT NULL,
  UNITS_NO             NUMBER,
  BATC_NO              NUMBER(25)               NOT NULL,
  SERIES_NO            VARCHAR2(20 CHAR),
  PRODUCTION_START_DT  DATE,
  PRODUCTION_END_DT    DATE
);

Insert into PODUCT_MANFACUTERS(OMERS_NO, UNITS_NO, BATC_NO,  SERIES_NO, PRODUCTION_START_DT, PRODUCTION_END_DT)
 Values(44668899, 1, 1,'ABND3U9', TO_DATE('11/15/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('11/15/2016 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into PODUCT_MANFACUTERS(OMERS_NO, UNITS_NO, BATC_NO,  PRODUCTION_START_DT, PRODUCTION_END_DT)
 Values(44668899, 1, 2, TO_DATE('11/15/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('11/15/2016 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into PODUCT_MANFACUTERS(OMERS_NO, UNITS_NO, BATC_NO,  SERIES_NO, PRODUCTION_START_DT, PRODUCTION_END_DT)
 Values(44668899, 2, 1, 'ABND3U9', TO_DATE('11/15/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('11/15/2016 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into PODUCT_MANFACUTERS(OMERS_NO, UNITS_NO, BATC_NO,  PRODUCTION_START_DT, PRODUCTION_END_DT)
 Values(44668899, 2, 2, TO_DATE('11/15/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('11/15/2016 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into PODUCT_MANFACUTERS(OMERS_NO, UNITS_NO, BATC_NO,  SERIES_NO, PRODUCTION_START_DT, PRODUCTION_END_DT)
 Values(22338855, 1, 1, 'ASMNDSC', TO_DATE('10/06/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/06/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into PODUCT_MANFACUTERS(OMERS_NO, UNITS_NO, BATC_NO)
 Values(22338855, 1, 2);
Insert into PODUCT_MANFACUTERS(OMERS_NO, UNITS_NO, BATC_NO,  SERIES_NO, PRODUCTION_START_DT, PRODUCTION_END_DT)
 Values(22338855, 4, 1, 'AQDFHNV', TO_DATE('10/07/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/07/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into PODUCT_MANFACUTERS(OMERS_NO, UNITS_NO, BATC_NO)
 Values(22338855, 4, 2);
Insert into PODUCT_MANFACUTERS(OMERS_NO, UNITS_NO, BATC_NO)
 Values(22338855, 4, 3);
Insert into PODUCT_MANFACUTERS(OMERS_NO, UNITS_NO, BATC_NO,SERIES_NO)
 Values(22338855, 7, 2,'SDFGO');
Insert into PODUCT_MANFACUTERS(OMERS_NO, UNITS_NO, BATC_NO)
 Values(22338855, 7, 3);
Insert into PODUCT_MANFACUTERS(OMERS_NO, UNITS_NO, BATC_NO)
 Values(22338855, 7, 4);
 Insert into PODUCT_MANFACUTERS(OMERS_NO, UNITS_NO, BATC_NO,  SERIES_NO, PRODUCTION_START_DT, PRODUCTION_END_DT)
 Values(22338855, 10, 1, 'HKMNBTE', TO_DATE('10/13/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/13/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
 Insert into PODUCT_MANFACUTERS(OMERS_NO, UNITS_NO, BATC_NO,  SERIES_NO, PRODUCTION_START_DT, PRODUCTION_END_DT)
 Values(22338855, 11, 1, 'HKMNBTE', TO_DATE('10/13/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/13/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
 Insert into PODUCT_MANFACUTERS(OMERS_NO, UNITS_NO, BATC_NO,  SERIES_NO, PRODUCTION_START_DT, PRODUCTION_END_DT)
 Values(66778899, 12, 1, 'AQSFCMF', TO_DATE('10/13/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/13/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
 Insert into PODUCT_MANFACUTERS(OMERS_NO, UNITS_NO, BATC_NO,  SERIES_NO, PRODUCTION_START_DT, PRODUCTION_END_DT)
 Values(66778899, 12, 2, 'AQSFCMF', TO_DATE('10/13/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/13/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
 COMMIT;

Please help me.

Thanks.

Report message to a moderator

Re: Updating missing values [message #628505 is a reply to message #628499]

Tue, 25 November 2014 13:41

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

I have data in a table as below.

How do you get this output, especially this order? Post the query.

Report message to a moderator

Re: Updating missing values [message #628523 is a reply to message #628505]

Wed, 26 November 2014 00:12

ramya_162
Messages: 107
Registered: August 2013
Location: Banglore

Senior Member

Hi,

This is the query I have used.

SELECT * FROM PODUCT_MANFACUTERS
ORDER BY OMERS_NO,UNITS_NO,BATC_NO;

Thanks.

Report message to a moderator

Re: Updating missing values [message #628527 is a reply to message #628523]

Wed, 26 November 2014 01:10

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

SQL> SELECT * FROM PODUCT_MANFACUTERS
  2  ORDER BY OMERS_NO,UNITS_NO,BATC_NO;
  OMERS_NO   UNITS_NO    BATC_NO SERIES_NO            PRODUCTION_START_DT PRODUCTION_END_DT
---------- ---------- ---------- -------------------- ------------------- -------------------
  22338855          1          1 ASMNDSC              06/10/2014 00:00:00 06/10/2015 00:00:00
  22338855          1          2
  22338855          4          1 AQDFHNV              07/10/2014 00:00:00 07/10/2015 00:00:00
  22338855          4          2
  22338855          4          3
  22338855          7          2 SDFGO
  22338855          7          3
  22338855          7          4
  22338855         10          1 HKMNBTE              13/10/2014 00:00:00 13/10/2015 00:00:00
  22338855         11          1 HKMNBTE              13/10/2014 00:00:00 13/10/2015 00:00:00
  44668899          1          1 ABND3U9              15/11/2014 00:00:00 15/11/2016 00:00:00
  44668899          1          2                      15/11/2014 00:00:00 15/11/2016 00:00:00
  44668899          2          1 ABND3U9              15/11/2014 00:00:00 15/11/2016 00:00:00
  44668899          2          2                      15/11/2014 00:00:00 15/11/2016 00:00:00
  66778899         12          1 AQSFCMF              13/10/2014 00:00:00 13/10/2015 00:00:00
  66778899         12          2 AQSFCMF              13/10/2014 00:00:00 13/10/2015 00:00:00

16 rows selected.

SQL> select OMERS_NO, UNITS_NO, BATC_NO,
  2         last_value(SERIES_NO ignore nulls) over (order by OMERS_NO,UNITS_NO,BATC_NO) SERIES_NO,
  3         last_value(PRODUCTION_START_DT ignore nulls) over (order by OMERS_NO,UNITS_NO,BATC_NO) PRODUCTION_START_DT,
  4         last_value(PRODUCTION_END_DT ignore nulls) over (order by OMERS_NO,UNITS_NO,BATC_NO) PRODUCTION_END_DT
  5  FROM PODUCT_MANFACUTERS
  6  ORDER BY OMERS_NO,UNITS_NO,BATC_NO;
  OMERS_NO   UNITS_NO    BATC_NO SERIES_NO            PRODUCTION_START_DT PRODUCTION_END_DT
---------- ---------- ---------- -------------------- ------------------- -------------------
  22338855          1          1 ASMNDSC              06/10/2014 00:00:00 06/10/2015 00:00:00
  22338855          1          2 ASMNDSC              06/10/2014 00:00:00 06/10/2015 00:00:00
  22338855          4          1 AQDFHNV              07/10/2014 00:00:00 07/10/2015 00:00:00
  22338855          4          2 AQDFHNV              07/10/2014 00:00:00 07/10/2015 00:00:00
  22338855          4          3 AQDFHNV              07/10/2014 00:00:00 07/10/2015 00:00:00
  22338855          7          2 SDFGO                07/10/2014 00:00:00 07/10/2015 00:00:00
  22338855          7          3 SDFGO                07/10/2014 00:00:00 07/10/2015 00:00:00
  22338855          7          4 SDFGO                07/10/2014 00:00:00 07/10/2015 00:00:00
  22338855         10          1 HKMNBTE              13/10/2014 00:00:00 13/10/2015 00:00:00
  22338855         11          1 HKMNBTE              13/10/2014 00:00:00 13/10/2015 00:00:00
  44668899          1          1 ABND3U9              15/11/2014 00:00:00 15/11/2016 00:00:00
  44668899          1          2 ABND3U9              15/11/2014 00:00:00 15/11/2016 00:00:00
  44668899          2          1 ABND3U9              15/11/2014 00:00:00 15/11/2016 00:00:00
  44668899          2          2 ABND3U9              15/11/2014 00:00:00 15/11/2016 00:00:00
  66778899         12          1 AQSFCMF              13/10/2014 00:00:00 13/10/2015 00:00:00
  66778899         12          2 AQSFCMF              13/10/2014 00:00:00 13/10/2015 00:00:00

16 rows selected.

Report message to a moderator

Re: Updating missing values [message #628538 is a reply to message #628527]

Wed, 26 November 2014 02:48

ramya_162
Messages: 107
Registered: August 2013
Location: Banglore

Senior Member

Hi,

Thanks for your help.

However I want the update query to update like.

After updating the result would be as below.

OMERS_NO    UNITS_NO   BATC_NO  SERIES_NO   PRODUCTION_START_DT  PRODUCTION_END_DT
22338855	1	1	ASMNDSC	     06/Oct/14	          06/Oct/15
22338855	1	2	ASMNDSC      06/Oct/14	          06/Oct/15
22338855	4	1	AQDFHNV	     07/Oct/14	          07/Oct/15
22338855	4	2	AQDFHNV	     07/Oct/14	          07/Oct/15
22338855	4	3	AQDFHNV	     07/Oct/14	          07/Oct/15
22338855	7	2	SDFGO	     07/Oct/14	          07/Oct/15
22338855	7	3	SDFGO	     07/Oct/14	          07/Oct/15
22338855	7	4	SDFGO        07/Oct/14	          07/Oct/15
22338855	10	1	HKMNBTE	    13/Oct/14	          13/Oct/15
22338855	11	1	HKMNBTE	    13/Oct/14	          13/Oct/15
44668899	1	1	ABND3U9	    15/Nov/14	          15/Nov/16
44668899	1	2	ABND3U9	    15/Nov/14	          15/Nov/16
44668899	2	1	ABND3U9	    15/Nov/14	          15/Nov/16
44668899	2	2	ABND3U9	    15/Nov/14	          15/Nov/16
66778899	12	1	AQSFCMF	    13-Oct-14	          13/Oct/15
66778899	12	2	AQSFCMF	    13/Oct/14	          13/Oct/15

Thats what I posted in my previous posts.

Please help me.

Thanks.

Report message to a moderator

Re: Updating missing values [message #628539 is a reply to message #628538]

Wed, 26 November 2014 02:54

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Where are the differences?
What does mean "with the parent value in BATCH_QUOTA table"?

Report message to a moderator

Re: Updating missing values [message #628541 is a reply to message #628539]

Wed, 26 November 2014 03:10

ramya_162
Messages: 107
Registered: August 2013
Location: Banglore

Senior Member

Hi,

I am extremely sorry.

By mistake instead of PODUCT_MANFACUTERS I have given BATCH_QUOTA.

I want to update the SERIES_NO,PRODUCTION_START_DT and PRODUCTION_END_DT which are missing for child records
with the parent value in PODUCT_MANFACUTERS table.

Please help me.

Thanks.

Report message to a moderator

Re: Updating missing values [message #628543 is a reply to message #628541]

Wed, 26 November 2014 03:12

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

What does mean "parent value in PODUCT_MANFACUTERS table"? What is the relation parent-child?
Where are the differences? Explain.

Report message to a moderator

Re: Updating missing values [message #628558 is a reply to message #628543]

Wed, 26 November 2014 05:18

ramya_162
Messages: 107
Registered: August 2013
Location: Banglore

Senior Member

Hi

In PODUCT_MANFACUTERS table for the same OMERS_NO and UNITS_NO more than one BATC_NO is existed,
first record is the parent and remaing records are child records.

In the following data

Parent is 22338855	1	1
Child is  22338855	1	2


22338855	1	1	ASMNDSC	     06/Oct/14	          06/Oct/15
22338855	1	2	             06/Oct/14	          06/Oct/15


Parent is 22338855	4	1
Child is  22338855	4	2
Child is  22338855	4	3


22338855	4	1	AQDFHNV	     07/Oct/14	          07/Oct/15
22338855	4	2		     07/Oct/14	          07/Oct/15
22338855	4	3		     07/Oct/14	          07/Oct/15

Please help me.

Thanks.

Report message to a moderator