Query to delete monthly data from a table where load is done weekly

Question

I have a table where data is loaded on weekly basis but I have to delete it on monthly basis. Scenario is like when the data is loaded it should delete all the data from the table for the present month. Table fields are like: Data_id - integer Data_week - Date/time - DD/MM/YYYY

Query written - Objective: To delete the entire 1 months data from the latest recent load. All Previous data to be retained.

DELETE FROM MHCD_AGG_CUST_ORG_SLS_WIDE_WK
WHERE DATA_WEEK >
  (SELECT DISTINCT(DATA_WEEK)
   FROM ADS.MHCD_AGG_CUST_ORG_SLS_WIDE_WK
   WHERE EXTRACT(MONTH FROM DATA_WEEK) !=
           EXTRACT(MONTH FROM (SELECT MAX(DATA_WEEK) FROM ADS.MHCD_AGG_CUST_ORG_SLS_WIDE_WK))
     AND EXTRACT(YEAR FROM DATA_WEEK) <=
           EXTRACT(YEAR FROM (SELECT MAX(DATA_WEEK) FROM ADS.MHCD_AGG_CUST_ORG_SLS_WIDE_WK))
   ORDER BY DATA_WEEK DESC);

Kindly help to correct the query as I am stuck to phrase the correct one.

Note that `DISTINCT` isn't a function, it's a _set quantifier_. `SELECT DISTINCT` will give you distinct _rows_. Simply write `SELECT DISTINCT DATA_WEEK...` to make code clearer, — jarlh, May 30 '23 at 08:26

NickW · Accepted Answer · 2023-05-30T13:12:15.933

1

Why not just do this:

WHERE to_char(data_week, 'yyyy-mm') = to_char(current_date, 'yyyy-mm')

Update following latest comment

WHERE to_char(data_week, 'yyyy-mm') = 
     (SELECT to_char(MAX(data_week), 'yyyy-mm') 
      FROM MHCD_AGG_CUST_ORG_SLS_WIDE_WK
     )

edited May 30 '23 at 13:12

answered May 30 '23 at 10:54

NickW

8,430
2
6
19

because I do not want to delete data based on current date to a month but based on the max(Data_week) available – Shalini Sharma May 30 '23 at 13:00
I've updated my answer - but just to point out, your question says "delete all the data from the table for the present month"; it doesn't say delete all data for the latest month for which there is data – NickW May 30 '23 at 13:13

score 0 · Answer 2 · edited May 31 '23 at 00:04

0

DELETE FROM MHCD_AGG_CUST_ORG_SLS_WIDE_WK
WHERE EXTRACT(MONTH FROM DATA_WEEK) = EXTRACT(MONTH FROM (SELECT MAX(DATA_WEEK) FROM ADS.MHCD_AGG_CUST_ORG_SLS_WIDE_WK))
  AND EXTRACT(YEAR FROM DATA_WEEK) = EXTRACT(YEAR FROM (SELECT MAX(DATA_WEEK) FROM ADS.MHCD_AGG_CUST_ORG_SLS_WIDE_WK));

edited May 31 '23 at 00:04

user16217248

3,119
19
19
37

answered May 30 '23 at 19:54

harperzhu

49
7

Query to delete monthly data from a table where load is done weekly

2 Answers2