SQL Update MS ACCESS

Question

UPDATE calendar SET eventDays = null WHERE ??

Problem:

168 eventDays | 24 weeks each day assigned a week number

if dayCount >=2 in a eventWeeks (24) remove eventDay for entire eventWeeks (7)

any eventWeek with total of just 1 would remove only that single date.

5/16/2017 to 5/22/2017 would remove date.

5/23/2017 to 5/29/2017 would just remove #5/24/2017#

do it for each group of eventWeeks (24)

eventDays | eventWeeks | dayCount
5/16/2017 | 1 | 1       
5/18/2017 | 1 | 0       
5/19/2017 | 1 | 0       
5/20/2017 | 1 | 0       
5/21/2017 | 1 | 1       
5/22/2017 | 1 | 0
5/23/2017 | 2 | 0       
5/24/2017 | 2 | 1       
5/25/2017 | 2 | 0       
5/26/2017 | 2 | 0       
5/27/2017 | 2 | 0       
5/28/2017 | 2 | 0       
5/29/2017 | 2 | 0

I tried several WHERE, HAVING and whatever, to no avail.

It's difficult to understand your requirements, but it seems like you might need to do this in a few passes, i.e. run a couple of update statements — Nick.Mc, Apr 28 '17 at 01:02
Are you sure that you aren't aiming to delete those records? — toonice, Apr 28 '17 at 01:27
To clarify, you want us to set the value of `eventDays` to `NULL` for an entire week where that week's total of `dayCount` is greater than or equal to `2` and otherwise to set the value of `eventDays` to `NULL` for any day with a `dayCount` of `1`? Would you rather delete an entire record rather than set its `eventDays` value to `NULL`? — toonice, Apr 28 '17 at 01:54
You are correct: also just delete that entire week if the day count >= 2 if that makes it easier. My client wants can only have 2 events per week, so if 2 people apply between tues and Monday remove the dates. — user3026911, Apr 28 '17 at 02:48
It's all tied to a third party Calendar which shows events (normal) Client is unnormal wants it "backwards completion principle". — user3026911, Apr 28 '17 at 02:55

score 0 · Accepted Answer · edited May 23 '17 at 11:54

0

This calculates the eventWeeks to be deleted.

SELECT eventweeks FROM calendar WHERE daycount = 2 
UNION 
SELECT eventweeks FROM calendar WHERE daycount = 1 
  GROUP BY eventweeks HAVING SUM(daycount) = 2;

This deletes all days of every week for the weeks found. This will not delete all days of a week, if dayCount = 3, as you requested. And it will not delete all days of a week, if 3 days have dayCount = 1 for a sum of 3, also by your request.

Query F

UPDATE calendar SET eventdays = NULL 
WHERE eventweeks IN (
  SELECT eventweeks FROM calendar WHERE daycount = 2 
  UNION 
  SELECT eventweeks FROM calendar WHERE daycount = 1 
    GROUP BY eventweeks HAVING SUM(daycount) = 2);

Query F behaviour is

eventWeeks | dayCount
    1      |     1    DELETE
    1      |     1    DELETE
    1      |     0    DELETE

    2      |     1    KEEP
    2      |     1    KEEP
    2      |     1    KEEP
    2      |     0    KEEP

    3      |     0    DELETE
    3      |     2    DELETE

    4      |     1    KEEP
    4      |     2    KEEP

EDIT A Because MS Access 2000 does not seem to accept UNION in subqueries, here is an alternative SELECT query (see SO):

SELECT DISTINCT eventweeks FROM calendar x 
WHERE daycount = 2 
  OR EXISTS (
    SELECT y.eventweeks FROM calendar y 
    WHERE x.eventweeks = y.eventweeks AND y.daycount = 1 
    GROUP BY y.eventweeks HAVING SUM(y.eventweeks) = 2
);

And the appropriate UPDATE

UPDATE calendar SET eventdays = NULL 
WHERE eventweeks IN (
  SELECT DISTINCT eventweeks FROM calendar x 
  WHERE daycount = 2 
    OR EXISTS (
      SELECT y.eventweeks FROM calendar y 
      WHERE x.eventweeks = y.eventweeks AND y.daycount = 1 
      GROUP BY y.eventweeks HAVING SUM(y.eventweeks) = 2
  ));

edited May 23 '17 at 11:54

Community

1
1

answered Apr 28 '17 at 01:08

flutter

694
3
8

You can use both WHERE to reduce the rows and then GROUP and check the group using HAVING with an aggregate function like COUNT. Example: SELECT eventweeks FROM calendar WHERE daycount >= 1 GROUP BY eventweeks HAVING COUNT(daycount) = 1; – flutter Apr 28 '17 at 01:27
Shouldn't that be `HAVING SUM( dayCount ) = 1`? – toonice Apr 28 '17 at 01:30
It depends on what you want. You can have SUM( dayCount ) = 2 with two rows with dayCount = 1 (1 + 1) or one single row with dayCount = 2. I think he wants dayCount at least one and then COUNT those that match. I don't quite understand his specification. – flutter Apr 28 '17 at 01:44
With a group of two values { 1, 2 }, we get SUM({ 1, 2 }) = 3, but COUNT({ 1, 2 }) = 2, not 3. – flutter Apr 28 '17 at 02:06
2nd one: This sets null ALL eventdays for weeks... almost got it Re: Question same day > Yes Someone can book 2 events on the same in on a given week. Only missing that part, one date had 2 but did not delete. – user3026911 Apr 28 '17 at 03:46
You can use two queries to achieve your result. Use "This sets null ALL eventdays for weeks" ... for the two days stuff. And use "This sets null the single eventday for weeks" ... for the one day only stuff. I will try to form the two into one single query. – flutter Apr 28 '17 at 04:06
Do you want to KEEP the dayCounts = 0 (equals zero) or do you want to DELETE them? Do you want to delete all days, if two events have dayCount >= 1? Or do you want only the events with dayCount >= 1 to be deleted? – flutter Apr 28 '17 at 04:13
Updated. Please see Edit: "This changes the last entry to dayCount = 2 for testing.". Is this ok? I know i can build what you want :) – flutter Apr 28 '17 at 04:25
Updated. Please see **EDIT 3** – flutter Apr 28 '17 at 04:34
Then **Query F** is what you are looking for. Please click the check mark next to the beginning of my answer to accept the solution. Also, please vote +1 by clicking ^ next to my answer. – flutter Apr 28 '17 at 14:57
Removed unneccessary parts of the answer to make the process faster. This is taking quite some time already... – flutter Apr 28 '17 at 16:35
I added another query, which does not use `UNION`, which MS Access 2000 does not seem to like in subqueries. Please check **EDIT A**. If it does still not work, please tell me. Now that the requirements are clear, it takes only minutes to write a new query. – flutter Apr 29 '17 at 00:24
Also, if the answer with **EDIT A** works, then please upvote it (triangle up; +1). – flutter Apr 30 '17 at 15:46
We used SQL= "UPDATE calendar SET eventdays = null WHERE eventWeeks IN ( SELECT eventWeeks FROM calendar WHERE dayCount >= 1 GROUP BY eventWeeks HAVING SUM(dayCount) >= 2);" Decided not to save > then 2. Works thanks to your SUM. Answer OK and voted up. – user3026911 May 01 '17 at 03:37

toonice · Answer 2 · 2017-04-28T04:54:12.740

Please try the following...

UPDATE calendar
SET eventDays = NULL
WHERE eventDays IN ( SELECT eventDays
                     FROM calendar
                     JOIN ( SELECT eventWeeks AS eventWeeks,
                                   SUM( dayCount ) AS daysCountSum
                            FROM calendar
                            GROUP BY eventWeeks
                          ) AS daysCounter ON calendar.eventWeeks = daysCounter.eventWeeks
                                          AND daysCounter.daysCountSum <= 1
                                          AND calendar.dayCount = 0
                   );

This statement works by first grouping all of the records in calendar by eventWeeks and calculating the total of dayCount for each group. The results are then joined to calendar in such a way that eventWeeks who have a total of 2 or more for dayCount are omitted, and only those records that have a 0 value for dayCountare included.

The resulting data is then used to select which records from calendar are updated.

If you have any questions or comments, then please feel free to post a Comment accordingly.

Simples to be missing a closing bracket. add one to the end by got syntax error. Using MS Access — user3026911, Apr 28 '17 at 03:29

SQL Update MS ACCESS

2 Answers2