Здравствуйте!

Дано:

Таблица неких записей, которые необходимо обработать, назовем ее Records,

--------------
ID | Text
--------------
 1 | Первый
 2 | Второй
 3 | Третий
 4 | Четвертый
--------------

Каждая запись назначается одному или нескольким пользователям.
Колонка Status отражает состояние обработки:
0 — данные ожидают обработки
1 — обработано
2 — отменено

Отменить обработку можно только в том случае, если никто из пользователей запись не обрабатывал.

Соответственно, данные о состоянии храним в таблице Statuses

--------------------------
RecordID | UserID | Status
--------------------------
 1       | 1      | 0
 1       | 2      | 1
 2       | 1      | 0
 2       | 2      | 0
 3       | 1      | 0
 3       | 2      | 0
 4       | 2      | 0
 5       | 1      | 0

Нужно отменить все обработку всех записей, назначенные конкретному пользователю, но с ограничениеми, которые я указал выше. Записи, которые уже были обработаны одним из пользователей, игнорируются.

Из примера, это должны быть записи, если отменять по пользователю №1: 2, 3, 5

Сделать выборку не составило проблемы

SELECT
    r.ID, r.Text, s2.UserID, s2.Status
FROM
    Records r
        INNER JOIN Statuses s1 ON r.ID        = s1.RecordID
        INNER JOIN Statuses s2 ON s1.RecordID = s2.RecordID
WHERE
    s1.UserID = 1 AND NOT EXISTS (SELECT d.Status FROM Statuses d WHERE d.RecordID = r.ID AND d.Status <> 0)

Результат выборки:
--------------------------
RecordID | UserID | Status
--------------------------
 2       | 1      | 0
 2       | 2      | 0
 3       | 1      | 0
 3       | 2      | 0
 5       | 1      | 0

а вот с обновлением не получается Ошибка: Error code 1093, SQL state HY000: You can't specify target table 'r' for update in FROM clause

UPDATE
    Records r
        INNER JOIN Statuses s1 ON r.ID        = s1.RecordID
        INNER JOIN Statuses s2 ON s1.RecordID = s2.RecordID
SET
    s2.Status = 2
WHERE
    s1.UserID = 1 AND NOT EXISTS (SELECT d.Status FROM Statuses d WHERE d.RecordID = r.ID AND d.Status <> 0)

Скрипт для создания структура и данные к ним.

CREATE TABLE Records(
    ID   INT         NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Text VARCHAR(10) NOT NULL
) ENGINE=InnoDB;


CREATE TABLE Statuses(
    RecordID INT NOT NULL,
    UserID   INT NOT NULL,
    Status   INT NOT NULL DEFAULT 0
) ENGINE=InnoDB;

ALTER TABLE Statuses ADD PRIMARY KEY (RecordID, UserID);
ALTER TABLE Statuses ADD INDEX       (Status);
ALTER TABLE Statuses 
    ADD FOREIGN KEY (RecordID) REFERENCES Records(ID) ON DELETE NO ACTION ON UPDATE NO ACTION;
    
INSERT INTO Records (Text) VALUES
    ("Первый"),
    ("Второй"),
    ("Третий"),
    ("Четвертый"),
    ("Пятый");
    
INSERT INTO Statuses VALUES
    (1, 1, 0),
    (1, 2, 1),
    (2, 1, 0),
    (2, 2, 0),
    (3, 1, 0),
    (3, 2, 0),
    (4, 2, 0),
    (5, 1, 0);

Здравствуйте, Аноним, Вы писали:

Можно конечно сделать таким образом, только эффективность мне совсем не нравится:

UPDATE
    Statuses
SET
    Status = 2
WHERE
    RecordID IN (
        SELECT
            upd.ID
        FROM (
            SELECT
                r.ID
            FROM
                Records r
                    INNER JOIN Statuses s1 ON r.ID        = s1.RecordID
                    INNER JOIN Statuses s2 ON s1.RecordID = s2.RecordID
            WHERE
                s1.UserID = 1 AND NOT EXISTS (SELECT d.Status FROM Statuses d WHERE d.RecordID = r.ID AND d.Status <> 0)
        ) AS upd
    )