CODEWITHSUNDEEP
mysqlsqlmysql-error-1093
Oyeme
Oyeme

Reputation: 11225

UPDATE column with join

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"

I need to UPDATE all currency from lottery_prizes table WHERE id's are from query above. I did this

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")

I have this error:
/* SQL Error (1093): You can't specify target table 'lottery_prizes' for update in FROM clause */ Thanks all

Upvotes: 1

Views: 1129

Answers (1)

Michael Pakhantsov
Michael Pakhantsov

Reputation: 25390

  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'

Upvotes: 6

Related Questions