logic for the Update statement

Database
Enthusiast

logic for the Update statement


Hi Gurus,

Here is the Output what i have tried through the lead function, i just got the result But i need to Update the Table as below result set . 

Can anyone share the Log for this I have tried but there is no luck Thanks


here is my output,  need an Update logic to update the records based in the effout_date 

but i want to update the records exist in the table

5th record of MFG_DATE - 30-Jun-14 is updated to 4th record of effout_date - 30-Jun-14 and

4th record of MFG_DATE - 29-Jun-14 is updated to 3rd record of data effout_date - 29-Jun-14

3rd record of MFG_DATE - 5-Jun-14 is updated to 2nd record of data effout_date - 5-Jun-14

DROP TABLE t_plant;

Create MULTISET Table t_plant
(
plant_no INTEGER NOT NULL
,plant_Name CHAR(9)
,part1 CHAR(9)
,part2 CHAR(9)
,part3 CHAR(9)
,mfg_date DATE FORMAT 'YY/MM/DD'
,effout_date DATE FORMAT 'YY/MM/DD'
,currency CHAR(9)
);

INSERT INTO t_plant
VALUES( 1,'Detroit','A11','B22','C33',TO_DATE( '2014-02-01', 'YYYY-MM-DD') , TO_DATE( '9999-12-31', 'YYYY-MM-DD'),'USD');

INSERT INTO t_plant
VALUES( 2,'Detroit','A11','B22','C33',TO_DATE( '2014-03-01', 'YYYY-MM-DD') , TO_DATE( '9999-12-31', 'YYYY-MM-DD'),'USD');

INSERT INTO t_plant
VALUES( 3,'Detroit','A11','B22','C33', TO_DATE( '2014-06-05', 'YYYY-MM-DD'), TO_DATE( '9999-12-31', 'YYYY-MM-DD'),'USD');

INSERT INTO t_plant
VALUES( 3,'Detroit','A11','B22','C33', TO_DATE( '2014-06-05', 'YYYY-MM-DD') , TO_DATE( '9999-12-31', 'YYYY-MM-DD'),'USD');

INSERT INTO t_plant
VALUES( 4,'Detroit','A11','B22','C33', TO_DATE( '2014-06-29', 'YYYY-MM-DD'), TO_DATE( '9999-12-31', 'YYYY-MM-DD'),'USD');

INSERT INTO t_plant
VALUES( 5,'Detroit','A11','B22','C33', TO_DATE( '2014-06-30', 'YYYY-MM-DD'), TO_DATE( '9999-12-31', 'YYYY-MM-DD'),'USD');

INSERT INTO t_plant
VALUES( 6,'INDIA','A10','B20','C30', TO_DATE( '2014-05-15', 'YYYY-MM-DD') , TO_DATE( '9999-12-31', 'YYYY-MM-DD'),'USD');

INSERT INTO t_plant
VALUES( 7,'INDIA','A10','B20','C30', TO_DATE( '2014-05-15', 'YYYY-MM-DD') , TO_DATE( '9999-12-31', 'YYYY-MM-DD'),'USD');

INSERT INTO t_plant
VALUES( 8,'INDIA','A10','B20','C30', TO_DATE( '2014-06-30', 'YYYY-MM-DD') , TO_DATE( '9999-12-31', 'YYYY-MM-DD'),'USD');






  part1 mfg_date Lead_COALESCE
1 A11       2/1/2014 3/1/2014
2 A11       3/1/2014 6/5/2014
3 A11       6/5/2014 6/29/2014
4 A11       6/29/2014 6/30/2014
5 A11       6/30/2014 12/31/9999
6 A10       5/15/2014 6/30/2014
7 A10       6/30/2014 12/31/9999

Select * FROM   t_plant Order by part1, part2, part3, mfg_date;

quries i am shring 

SELECT plant_Name,part1, part2, part3, mfg_date

      ,COALESCE(  MIN(mfg_date)

OVER(PARTITION BY  plant_Name,PART1, part2, part3  ORDER BY mfg_date

ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING )

,CAST('9999-12-31'AS DATE) ) AS effout_date_Lead_COALESCE --Tomorrow

 FROM   t_plant

 GROUP BY  plant_Name,PART1, PART2, PART3, mfg_date

 ;

Select * FROM   t_plant Order by part1, part2, part3, mfg_date;

Result set



    plant_no plant_Name     part1        part2        part3         mfg_date effout_date currency
1 1 Detroit A11 B22 C33 2/1/2014 12/31/9999 USD
2 2 Detroit A11 B22 C33 3/1/2014 12/31/9999 USD
3 3 Detroit A11 B22 C33 6/5/2014 12/31/9999 USD
4 3 Detroit A11 B22 C33 6/5/2014 12/31/9999 USD
5 4 Detroit A11 B22 C33 6/29/2014 12/31/9999 USD
6 5 Detroit A11 B22 C33 6/30/2014 12/31/9999 USD
7 7 INDIA A10 B20 C30 5/15/2014 12/31/9999 USD
8 6 INDIA A10 B20 C30 5/15/2014 12/31/9999 USD
9 8 INDIA A10 B20 C30 6/30/2014 12/31/9999 USD

Here is the Output what i have tried through the lead function, i just got the result But i need to Update the Table as below result set .

Can anyone share the Log for this I have tried but there is no luck Thanks

Select t_plant_1.* , t_plant_2.effout_date
From t_plant t_plant_1
,
(
SELECT plant_Name,PART1, PART2, PART3, mfg_date
,COALESCE( MIN(mfg_date)
OVER(PARTITION BY plant_Name,PART1, PART2, PART3 ORDER BY mfg_date
ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING )
,CAST('9999-12-31'AS DATE) ) AS effout_date --Lead_COALESCE
FROM t_plant
GROUP BY plant_Name,PART1, PART2, PART3, mfg_date
) t_plant_2
Where t_plant_1.plant_Name = t_plant_2.plant_Name
and t_plant_1.PART1 = t_plant_2.PART1
and t_plant_1.PART2 = t_plant_2.PART2
and t_plant_1.PART3 = t_plant_2.PART3
and t_plant_1.mfg_date = t_plant_2.mfg_date
Order by t_plant_1.plant_Name,t_plant_1.PART1 ,t_plant_1.PART2,t_plant_1.PART3,t_plant_1.mfg_date

OUT/PUT

   plant_no plant_Name part1         part2        part3         mfg_date effout_date_lead currency
1 1 Detroit A11 B22 C33 2/1/2014 3/1/2014 USD
2 2 Detroit A11 B22 C33 3/1/2014 6/5/2014 USD
3 3 Detroit A11 B22 C33 6/5/2014 6/29/2014 USD
4 3 Detroit A11 B22 C33 6/5/2014 6/29/2014 USD
5 4 Detroit A11 B22 C33 6/29/2014 6/30/2014 USD
6 5 Detroit A11 B22 C33 6/30/2014 12/31/9999 USD
7 7 INDIA A10 B20 C30 5/15/2014 6/30/2014 USD
8 6 INDIA A10 B20 C30 5/15/2014 6/30/2014 USD
9 8 INDIA A10 B20 C30 6/30/2014 12/31/9999 USD

what i have tried 

Update t_plant
FROM
(
SELECT t_plant_1.*
FROM t_plant t_plant_1
,
(
SELECT plant_Name,PART1, PART2, PART3, mfg_date
,COALESCE( MIN(mfg_date)
OVER(PARTITION BY plant_Name,PART1, PART2, PART3 ORDER BY mfg_date
ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING )
,CAST('9999-12-31'AS DATE) ) AS effout_date --Lead_COALESCE
FROM t_plant
GROUP BY plant_Name,PART1, PART2, PART3, mfg_date
) t_plant_2
WHERE t_plant_1.plant_Name = t_plant_2.plant_Name
AND t_plant_1.PART1 = t_plant_2.PART1
AND t_plant_1.PART2 = t_plant_2.PART2
AND t_plant_1.PART3 = t_plant_2.PART3
AND t_plant_1.mfg_date = t_plant_2.mfg_date
ORDER BY t_plant_1.plant_Name,t_plant_1.PART1 ,t_plant_1.PART2,t_plant_1.PART3,t_plant_1.mfg_date
) B1
SET effout_date = B1.effout_date
WHERE plant_Name = B1.plant_Name
AND PART1 = B1.PART1
AND PART2 = B1.PART2
AND PART3 = B1.PART3
AND mfg_date = B1.mfg_date
;
1 REPLY
Enthusiast

Re: logic for the Update statement

Here, i got the answer from the @dnoeth  in different forum and Thank you very much to  @dnoeth 

Your final Select was close, you just need to replace the Select part with an Update:

update t_plant 
from
(
SELECT plant_Name,PART1, PART2, PART3, mfg_date
,COALESCE( MIN(mfg_date)
OVER(PARTITION BY plant_Name,PART1, PART2, PART3 ORDER BY mfg_date
ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING )
,CAST('9999-12-31'AS DATE) ) AS effout_date --Lead_COALESCE
FROM t_plant
GROUP BY plant_Name,PART1, PART2, PART3, mfg_date
) t_plant_2
SET effout_date = t_plant_2.effout_date
Where t_plant.plant_Name = t_plant_2.plant_Name
and t_plant.PART1 = t_plant_2.PART1
and t_plant.PART2 = t_plant_2.PART2
and t_plant.PART3 = t_plant_2.PART3
and t_plant.mfg_date = t_plant_2.mfg_date