How to update just the oldest record using a datetime field?

Question

Ok so I have this waiting list with people already registered and I have a "NEXT PERSON" button that calls a PHP script with AJAX that is supposed to eliminate the oldest record (in other words the next person) from the waiting list.

Well I'm trying to build this query that only updates the oldest person in the table that has not yet been attended. Here is my query:

UPDATE queue
SET ATTENDED='1' 
WHERE VISIT_DATE = (SELECT * WHERE ATTENDED='0' ORDER BY VISIT_DATE ASC LIMIT 1)

Here is my table (queue):

ID      int(11)
GUEST_NAME  varchar(50)
VISIT_DATE  datetime(6)
ATTENDED    int(11)

The problem is that I'm getting the error "MySQL error 1241: Operand should contain 1 column(s)".

I'm doing it wrong?

Where is the table you are selecting for? i guess should be "SELECT * FROM QUEUE WHERE ATTENDED='0' ORDER BY VISIT_DATE ASC LIMIT 1 — Diego Vinícius, Jun 28 '18 at 11:50
On a sidenote: `ATTENDED = '0' / '1'`? You haven't included the column's definition, but it's probably numeric, so you should use numbers (`1` / `2`), not strings (`'1'` / `'2'`). — Thorsten Kettner, Jun 28 '18 at 11:51
Yes I forgot that. I just updated my question and added it. Thanks for the suggestions. — Oscar Otero, Jun 28 '18 at 13:51

score 4 · Accepted Answer · edited Jun 28 '18 at 11:37

4

The issue is in your WHERE clause. You're comparing VISIT_DATE to the entire row of a table.

As @Hudson's pointed out, you're also missing the table from which you're getting the oldest date. Therefore, your query should look something like this:

UPDATE 
    queue AS UpdateTbl
SET 
    UpdateTbl.ATTENDED='1' 
WHERE 
    UpdateTbl.VISIT_DATE = (SELECT 
                      ReadTbl.VISIT_DATE 
                  FROM
                      (select * from queue) AS ReadTbl
                  WHERE 
                      ReadTbl.ATTENDED='0' 
                  ORDER BY 
                      ReadTbl.VISIT_DATE ASC 
                  LIMIT 1)

Looking at: Table is specified twice, both as a target for 'UPDATE' and as a separate source for data in mysql

edited Jun 28 '18 at 11:37

Oscar Otero

325
3
11

answered Jun 27 '18 at 16:24

kchason

2,836
19
25

Thank you both for your answers. I tried using your query but now I'm getting the error: "#1093 - Table 'queue' is specified twice, both as a target for 'UPDATE' and as a separate source for data". Any idea? – Oscar Otero Jun 27 '18 at 17:35
Ah yes, my answer was updated, you need to alias the tables – kchason Jun 27 '18 at 18:30
I updated the query but now it says: "#1093 - Table 'UpdateTbl' is specified twice, both as a target for 'UPDATE' and as a separate source for data". Any other suggestion? – Oscar Otero Jun 27 '18 at 18:40
You used different aliases right? I added aliases to all the columns and a link to a question that pointed that out – kchason Jun 27 '18 at 18:47
No, I've never used them. I tried the new query but I keep receiving the same error. – Oscar Otero Jun 27 '18 at 19:27
1

I followed the instructions in the link. I changed "FROM queue AS ReadTbl" to "FROM (select * from queue) AS ReadTbl" and now works. Thank you very much for you help. Updating solution. – Oscar Otero Jun 28 '18 at 11:26

score 1 · Answer 2 · answered Jun 28 '18 at 11:48

First of all: can there be multiple records with the same date? Then you must make use of your ID:

UPDATE queue
SET attended = 1
WHERE id = 
(
  SELECT id 
  WHERE attended = 0
  FROM (SELECT * FROM queue) q
  ORDER BY visit_date, id
  LIMIT 1
);

Otherwise it's just the minimum date:

UPDATE queue
SET attended = 1
WHERE visit_date = 
(
  SELECT MIN(visit_date)
  FROM (SELECT * FROM queue) q
  WHERE attended = 0
);

In both cases we must select FROM (SELECT * FROM queue) q instead of a mere FROM queue, because MySQL forbids to query the updated table directly for unknown reasons.

How to update just the oldest record using a datetime field?

2 Answers2