MYSQL update a row if all multiple rows that belong to the same foreign ID have same value

Question

I have 3 tables: schedule and locations, suites.

Schedule's structure: schedule-id, location-id, schedule-completed-on-date

Locations' structure: location-id, location-building-number, location-street-name

Suites' structure: suite-id, location-id, suite-number, suite-visited

Many suites may belong to same location. Schedule's location-id is unique, so there may be only one unique location scheduled at any time, no duplicates.

I want to set schedule-completed-date-on to a date, which I know how to do, if every single suite for a location of a schedule row have suite-visited equal to 1 (0- no, 1- yes).

Is it possible to do with MySQL, and if so then how? Or do I have to do it programmatically using a server side language?

For example, if I have two locations - 1 Main St., and 2 Main St. - and 10 suites in total - 5 belong to first location, other 5 belong to second location. If MySQL detects that all 5 suite rows that relate to first location have suite-visited equal to 1, then immediately set schedule-completed-on to current date, otherwise don't.

Data at hand and desired result:

LOCATIONS
location-id     location-building-number    location-street-name
    1                   1                           Main St.
    2                   2                           Main St.

SUITES
suite-id    location-id     suite-number    suite-visited
    1           1               100             1
    2           1               200             0
    3           1               300             0
    4           1               400             0
    5           1               500             1
    6           2               1000            1
    7           2               1100            1
    8           2               1200            1
    9           2               1300            1
    10          2               1400            1

SCHEDULE
schedule-id     location-id     schedule-completed-on-date
    1               1                   NULL
    2               2               7/3/2015 00:00:00

// because all suites for location 2 have suite-visited set to 1
// second row under SCHEDULE gets the date set 
// because all suites belonging to
// location 2 have been visited

You might be able to achieve using the inverse, I.e. update the records if no records has site-visited =0, using UPDATE statement along with NOT EXISTS — Techie, Jul 03 '15 at 16:36
With the structure like it is, you could remove table ´schedule´ (schedule_id = location_id) and add a column ´suite-visited-on-date´ to the suites table, update that column when you update suite-visited to 1. Then you can check for each location if there is a suite-visited = 0 or not, and if not select the latest date. — Zimmi, Jul 03 '15 at 17:08

Techie · Accepted Answer · 2015-07-03T19:17:35.043

1

Trigger for auto update of SCHEDULE based on update of suite-visited table, name the trigger appropriately.

DELIMITER $$      
  CREATE TRIGGER trigger_name
  AFTER UPDATE
     ON suite-visited FOR EACH ROW      
  BEGIN      
      IF NOT EXISTS (select 1 from SUITES where suite-visited = 0 and location-id = new.location-id) THEN
          UPDATE SCHEDULE set schedule-completed-on-date = curdate() where location-id = new.location-id;
      END IF;
  END;
$$
DELIMITER ;

edited Jul 03 '15 at 19:17

answered Jul 03 '15 at 17:02

Techie

1,491
3
18
24

I'm having hard time understanding this query. You try to set completed-on and location-id in data that was returned by where clause which says "return 1 row from suites where suite-visited is 0 and location-id is 1"? – BaddieProgrammer Jul 03 '15 at 17:16
So this is for server side scripting approach? What I was wondering is if there's a way build-it into MySQL to monitor rows like this and make changes if certain conditions are met. – BaddieProgrammer Jul 03 '15 at 17:22
So are you looking out for scheduling code execution on MySQL or the sql syntax to update the relevant rows? – Techie Jul 03 '15 at 17:35
Well, that's the thing I was wondering if scheduling will be better that writing an actual PHP script. – BaddieProgrammer Jul 03 '15 at 18:04
You can use Event Scheduler on MySQL to run the code on daily basis and update the rows with current date curdate() Refer [link1](http://www.sitepoint.com/how-to-create-mysql-events/), [link2](https://dev.mysql.com/doc/refman/5.1/en/events.html) Scheduling can be done if you don't want any user input and it needs to be executed without being forgotten. I haven't use this but you can try if it matches your requirement. – Techie Jul 03 '15 at 18:09
Hm, problem is this operation should not run daily but sort of monitor the tables 24/7. Do you think using PHP would be better by firing a call to a function that will check the tables – BaddieProgrammer Jul 03 '15 at 18:18
What you want is trigger, check the updated answer if it works. Please mention complete requirement to get appropriate response. Similar question was already posted [here](http://stackoverflow.com/questions/15975877/mysql-trigger-on-insert-update-events) – Techie Jul 03 '15 at 19:14

MYSQL update a row if all multiple rows that belong to the same foreign ID have same value

1 Answers1