英文:
Perform UPDATE statement only if SELECT statement gives empty set in MySQL
问题
我有一个MySQL表格如下：
create table login_schedule(name varchar(20),emp_id varchar(5),login_time datetime,logout_time datetime);
数据插入如下：
insert into login_schedule values('Ram','01',now(),now()+interval 30 minute);
所以，我想要执行一个更新查询，只有当一个选择语句返回空集结果时才执行。
以下是我的选择语句：
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');
我的更新语句如下：
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';
所以，请帮忙。我主要要实现的是，如果员工在分配的登出时间（登录后30分钟）之前执行任何操作，那么他的登出时间将增加30分钟。如果用户闲置30分钟，他将无法执行任何操作，他必须再次登录以获得另外30分钟。
所以，为了实现这一点，第一个选择查询必须返回空集，然后才能执行更新查询，否则如果选择查询返回任何输出，那么更新查询将不会执行。
请帮忙。
英文:
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.
答案1
得分: 1
不需要执行SELECT查询。您所描述的是，只有在时间尚未过去的情况下，才希望将注销时间延长到NOW() + 30分钟。
这个更新将实现这一目标：
UPDATE login_schedule
SET logout_time = CASE
WHEN logout_time > NOW() THEN NOW() + INTERVAL 30 MINUTE
ELSE logout_time END
WHERE emp_id = '01';
英文:
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';
答案2
得分: 0
> 如果一个选择语句返回空集结果，我想执行更新查询
UPDATE {要更新的表}
SET {列 = 值}
WHERE {其他条件}
AND NOT EXISTS ( {必须返回空集结果的一个选择语句} )
> 这个解决方案给我以下错误: ERROR 1093 (HY000): 不能在FROM子句中指定目标表'login_schedule'进行更新。- Sanghamitra Lahiri
UPDATE {要更新的表}
CROSS JOIN ( {必须返回空集结果的一个选择语句 LIMIT 1 } ) x
SET {列 = 值}
WHERE {其他条件}
<details>
<summary>英文:</summary>
> 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}
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论