There are two tables.

  1. records - list of requests. Columns:
    numcar - car number
    timepriezd - estimated time of arrival of the car (timestamp)
    pk is a unique identifier, the default is NULL
    active - the status of the passage of the machine. (1 - if the car has not yet passed)
  2. recognized_numbers - list of recognized numbers. Columns:
    numcar is the number of the recognized machine
    time - the time when the car drove ( timestamp )
    type - passage status (1 - entry, 2 - exit)
    pk is a unique identifier, NOT NULL ( VARCHAR (250) )

It is necessary to make a request that will do the following: Find all applications that have a travel status of '1' ( active = '1' ), replace it with '2'; replace the time of the intended passage, at the time of the actual passage. It is important to note that the car can travel more than once during the day and all the time. DB - MySQL

  • And you have something failed? What exactly did you do and failed? - Alexey Shimansky
  • I can’t think of a request that does this. - drstannum
  • First you need to formulate in words what kind of record from the applications you want to find for a specific record from the recognized ones. There is an entry / exit fare status, which of the entries should be considered both or only entry. If there are several applications for one number, which of them should be taken for a specific fact of travel. What and when to do with the status - apparently it should be transferred from active to 0 at the first passage on the application, or not? - Mike
  • All who have active status = 1. Only those who enter. If there are several applications - the one that is closest to the intended passage, but not earlier. - drstannum
  • “but not before,” so be it of course, but it seems strange, the car got stuck in traffic, it was delayed ... Although of course in the application it is possible that the time with a reserve is worth - Mike

1 answer 1

Help answering - Mike

The essence of the inability to make a similar request:

MySQL does not allow the use of update subqueries (at least in from, at least in set) the same table that changes.

In this regard, it was necessary to write a script on an external PL that performs all actions.