Question 20896 in Mysql Question posted in
The official MySQL documentation can be found here.

Perform UPDATE statement only if SELECT statement gives empty set in MySQL

I have a MySQL table like below:

create table login_schedule(name varchar(20),emp_id varchar(5),login_time datetime,logout_time datetime);

and data insertion looks like below:

insert into login_schedule values('Ram','01',now(),now()+interval 30 minute);

So, I want to perform a update query only if one select statement gives empty set result.

Below is my select statement:

select name,emp_id,logout_time,login_time from login_schedule where emp_id='01' and now()<date_format(logout_time,'%Y-%m-%d %H:%i:%s');

My Update statement is like below:

update login_schedule set logout_time=(select date_format(now() + interval 30 minute,'%Y-%m-%d %H:%i:%s') from dual) where emp_id='01';

So, is there any way please help. Mainly what I have to achieve is, if employee perform any action before his allocated logout time (which is 30 minutes after login time), then his logout time will increases to another 30 minutes. If users stays idle for 30 minutes then he can’t perform any action, he have to login again to get another 30 minutes.

So, to achieve this first select query must return empty set then only update query will be performed, else if select query gives any output then update query will not going to perform.

Please help.

Tags:

2

Answers


  1. 0

    I want to perform a update query only if one select statement gives empty set result

    UPDATE {tables to be updated}
SET {columns = values}
WHERE {other conditions}
  AND NOT EXISTS ( {one select statement which must give empty set result} )

    his solution gives me the below error: ERROR 1093 (HY000): You can’t specify target table ‘login_schedule’ for update in FROM clause. – Sanghamitra Lahiri

    UPDATE {tables to be updated}
CROSS JOIN ( {one select statement which must give empty set result} LIMIT 1 ) x
SET {columns = values}
WHERE {other conditions}
    Login or Signup to reply.
  2. 0

    I don’t think you need to do the SELECT query at all. What you described is that you want to extend the logout time to NOW() + 30 minutes if and only if that time has not yet passed.

    This update would accomplish that:

    UPDATE login_schedule
SET logout_time = CASE 
  WHEN logout_time > NOW() THEN NOW() + INTERVAL 30 MINUTE 
  ELSE logout_time END
WHERE emp_id = '01';
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search