使用连接更新列

Question

SELECT lott.id as lottery_id,lott.abbr,lott.currency,payments.id as payment_id,
    payment_prizes.prize_id,prizes.name,prizes.currency as prizes_currency
    FROM lotteries lott 
    JOIN lottery_payments payments 
        ON
        payments.lottery_id =  lott.id
    JOIN lottery_payment_prizes payment_prizes
        ON
        payment_prizes.payment_id = payments.id
    JOIN lottery_prizes prizes
        ON
        prizes.id =  payment_prizes.prize_id
    WHERE lott.currency = "ID"

我需要更新 lottery_prizes 表中的所有货币，其中 id 来自上面的查询。 我这样做时

UPDATE lottery_prizes SET currnecy = 'ID'
 WHERE id IN(SELECT prizes.id
    FROM lotteries lott 
    JOIN lottery_payments payments 
        ON
        payments.lottery_id =  lott.id
    JOIN lottery_payment_prizes payment_prizes
        ON
        payment_prizes.payment_id = payments.id
    JOIN lottery_prizes prizes
        ON
        prizes.id =  payment_prizes.prize_id
    WHERE lott.currency = "ID")

出现以下错误：
/* SQL 错误 (1093): 您无法在 FROM 子句中指定用于更新的目标表 'lottery_prizes' */ 谢谢大家

Answer 1

  UPDATE lottery_prizes l 
   JOIN (SELECT prizes.id
      FROM lotteries lott 
      JOIN lottery_payments payments 
          ON
          payments.lottery_id =  lott.id
      JOIN lottery_payment_prizes payment_prizes
          ON
          payment_prizes.payment_id = payments.id
      JOIN lottery_prizes prizes
          ON
          prizes.id =  payment_prizes.prize_id
      WHERE lott.currency = "ID") t
      ON t.Id = l.Id
 SET currency = 'ID'