时间戳

首页 > 解决方案 > 用于更新特定记录的 SQL 语句，但如果该记录在列中包含某个值，则忽略该记录

mysql - 用于更新特定记录的 SQL 语句，但如果该记录在列中包含某个值，则忽略该记录

问题描述

正如标题所示，我需要更新星期几的时间，但忽略星期几等于星期六或星期日的记录。该查询似乎还更新了包括星期六和星期日的记录，这是我试图避免的。

UPDATE openingTimes SET timeOpening = '09:00:00', timeClosing = '18:00:00'
    WHERE departmentId=79
    OR departmentId=3
    OR departmentId=6
    OR departmentId=10
    OR departmentId=15
    OR departmentId=18
    OR departmentId=25
    OR departmentId=29
    OR departmentId=32
    OR departmentId=36
    OR departmentId=40
    OR departmentId=44
    OR departmentId=49
    OR departmentId=54
    OR departmentId=57
    OR departmentId=61
    OR departmentId=67
    OR departmentId=72
    OR departmentId=79
    OR departmentId=83
    OR departmentId=84
    OR departmentId=89
    OR departmentId=95
    OR departmentId=99
    OR departmentId=113
    OR departmentId=118
    OR departmentId=120
    OR departmentId=124
    OR departmentId=131
    OR departmentId=136
    OR departmentId=137
    OR departmentId=142
    OR departmentId=145
    OR departmentId=149
    OR departmentId=157
    OR departmentId=162
    AND NOT (dayOfTheWeek='Saturday' OR dayOfTheWeek='Sunday');

标签： mysqlsql

解决方案

请使用下面的查询，这是正确的方法，而不是使用多个 or 条件。

UPDATE openingTimes SET timeOpening = '09:00:00', timeClosing = '18:00:00'
WHERE departmentId in
(79,3,6,10,15,18,25,29,32,36,40,44,49,54,57,61,67,72,79,83,84,89,95,99,
113,118,120,124,131,136,137,142,145,149,157,162)    
AND dayOfTheWeek NOT in  ('Saturday', 'Sunday');

推荐阅读