Как обнулить дату, если прошло больше года?

Question

Написал следующий код:

CREATE TABLE group1 (
   id INTEGER PRIMARY KEY,
   name TEXT NOT NULL,
   sex TEXT NOT NULL,
   vaccine BOOLEAN NOT NULL,
   vaccine_date DATE
);
CREATE TABLE group2 (
   id INTEGER PRIMARY KEY,
   name TEXT NOT NULL,
   sex TEXT NOT NULL,
   vaccine BOOLEAN NOT NULL,
   vaccine_date DATE
);
INSERT INTO group1 VALUES (1, 'Ryan', 'M', False, NULL);
INSERT INTO group1 VALUES (2, 'Joanna', 'w', True, '2021-09-09');
INSERT INTO group2 VALUES (1, 'Steve', 'M', True, '2022-01-21');
INSERT INTO group2 VALUES (2, 'Chris', 'M', False, NULL);
IF (vaccine FROM group1 = True) AND (DATEDIFF(CURDATE(), vaccine_date FROM group1) > 365):
   BEGIN:
      SELECT vaccine FROM group1 = False;
      SELECT vaccine_date FROM group1 = NULL;
   END
IF (vaccine FROM group2 = True) AND (DATEDIFF(CURDATE(), vaccine_date FROM group2) > 365):
   BEGIN
      SELECT vaccine FROM group2 = False;
      SELECT vaccine_date FROM group2 = NULL;
  END;

Моя цель - чтобы в обеих таблицах, если с даты vaccine_date прошёл год, значение vaccine менялось на False, а vaccine_date - на NULL. Выдаёт ошибку:

Error: near line 19: in prepare, near "IF": syntax error (1)
Error: near line 22: in prepare, near "=": syntax error (1)
Error: near line 23: stepping, cannot commit - no transaction is active (1)
Error: near line 24: in prepare, near "IF": syntax error (1)
Error: near line 27: in prepare, near "=": syntax error (1)
Error: near line 28: stepping, cannot commit - no transaction is active (1)

Answer 1

Для того, что бы записать значение null в поле, где прошло более 365 дней, необходимо выполнить update:

update group1
set vaccine_date = null,
    vaccine = false
where DATEDIFF(CURDATE(), vaccine_date) > 365
AND vaccine = True;

update group2
set vaccine_date = null,
    vaccine = false
where DATEDIFF(CURDATE(), vaccine_date) > 365
AND vaccine = True;

Полный код для воспроизведения:

CREATE TABLE group1 (
   id INTEGER PRIMARY KEY,
   name TEXT NOT NULL,
   sex TEXT NOT NULL,
   vaccine BOOLEAN NOT NULL,
   vaccine_date DATE
);
CREATE TABLE group2 (
   id INTEGER PRIMARY KEY,
   name TEXT NOT NULL,
   sex TEXT NOT NULL,
   vaccine BOOLEAN NOT NULL,
   vaccine_date DATE
);
INSERT INTO group1 VALUES (1, 'Ryan', 'M', False, NULL);
INSERT INTO group1 VALUES (2, 'Joanna', 'w', True, '2021-09-09');
INSERT INTO group1 VALUES (3, 'Bonna', 'w', True, '2022-07-09');
INSERT INTO group2 VALUES (1, 'Steve', 'M', True, '2022-01-21');
INSERT INTO group2 VALUES (2, 'Chris', 'M', False, NULL);
INSERT INTO group2 VALUES (3, 'Jim', 'M', True, '2022-03-09');

select * from group1;
select * from group2;

update group1
set vaccine_date = null,
    vaccine = false
where DATEDIFF(CURDATE(), vaccine_date) > 365
AND vaccine = True;

update group2
set vaccine_date = null,
    vaccine = false
where DATEDIFF(CURDATE(), vaccine_date) > 365
AND vaccine = True;

select * from group1;
select * from group2;

БЛОГ НА HUSL

Как обнулить дату, если прошло больше года?

Ответы (1 шт):