Mysql ввел SELECT в UPDATE из той же таблицы

По сути, мне нужно сделать что-то вроде этого... это всего лишь пример... но синтаксис первого запроса не работает в MySQL

update people set prize = '' 
where prize = 'Gold' and class = (select class from people where id = person_id);
update people set prize = 'Gold' where id = <id>;

Только один человек может получить Золотой приз в любом классе. Я знаю только человека, который получает Золотой приз.

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

Я считаю, что мне нужно использовать некоторый тип внутреннего соединения, но я не уверен в этом на 100%.

Что было бы даже умнее, если бы я мог сделать всю работу в одном запросе!

Может ли кто-нибудь давать совет?

Спасибо:)

Ответ 1

UPDATE people
SET    prize = CASE WHEN id = @lucky THEN 'Gold' ELSE 'Silver' END
WHERE  class = (
       SELECT  class
       FROM    people
       WHERE   id = @lucky
       )

Это предоставит всем пользователям приз Silver, кроме @lucky, который получит Gold.

Если вам нужно только обновить @lucky и экс-чемпиона, выполните следующие действия:

UPDATE people
SET    prize = CASE WHEN id = @lucky THEN 'Gold' ELSE 'Silver' END
WHERE  id = @lucky
       OR (class, prize) =
       (
       SELECT  class, 'Gold'
       FROM    people
       WHERE   id = @lucky
       )

Ответ 2

Не всегда лучше делать сложные обновления в одном SQL-запросе. Фактически, было бы более эффективно выполнять два простых запроса. Поэтому не забудьте проверить оба решения.

MySQL поддерживает расширение для синтаксиса UPDATE для обновления нескольких таблиц. Вы можете выполнить JOIN как часть обновления вместо использования подзапросов.

Затем вы можете использовать функцию IF() (или CASE), чтобы условно изменить значение prize на разные значения.

Итак, если вы абсолютно должны использовать один запрос, попробуйте что-то вроде этого:

UPDATE people p1 JOIN people p2 
  ON (p1.class = p2.class AND p2.id = <person_id>)
SET prize = IF(p1.id = <person_id>, 'Gold', '')
WHERE p1.id = <person_id> OR p1.prize = 'Gold';

Или эта альтернатива:

UPDATE people p1 JOIN people p2 
  ON (p1.class = p2.class AND p2.id = <person_id>)
SET p1.prize = CASE 
  WHEN p1.id = <person_id> THEN 'Gold'
  WHEN p1.prize = 'Gold' THEN ''
  ELSE p1.prize -- other cases are left as is
 END CASE;

Ответ 3

Если вы используете один оператор, вы можете использовать оператор CASE? Не используется MySQL, но что-то вроде:

UPDATE people
SET prize = CASE
              WHEN 'Gold' AND id != @newid THEN ''
              WHEN '' AND id = @newid THEN 'Gold'
            END 
WHERE class = ( 
                SELECT class 
                FROM people
                WHERE id = @newid 
              )

Ответ 4

Вы можете определенно создать хранимую процедуру, которая в качестве аргумента примет идентификатор нового победителя.

Затем вы можете вызвать эту хранимую процедуру в одной строке.

Я использую MS SQL здесь, но что-то похожее на:

CREATE PROC UpdatePrize
  @newid int

AS

UPDATE people
SET prize = '' 
WHERE prize = 'Gold'
AND class = ( SELECT class 
              FROM people
              WHERE id = @newid )

UPDATE people
SET prize = 'Gold'
WHERE id = @newid

GO

Ответ 5

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

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