Cross table update join operation works in MySQL but not SQLite

Asked Jun 27 '19 at 19:14

Active Jun 27 '19 at 19:14

Viewed 28 times

I have this cross table update join operation in MySQL that works perfectly but when executed against SQLite it wouldn't work.

The error message given by SQLite was quite vague and I wasn't sure what's wrong or different between both SQLs that is causing the problem.

How can I get it working for SQLite?

Query that works in MySQL:

update
  `entries`
  inner join `transactions` on `entries`.`transaction_id` = `transactions`.`id`
  and `transactions`.`batch_id` = 4
  and `entries`.`number` = "2010"
set
  `entries`.`prize` = "consolation",
  `entries`.`winnings` = entries.amount * 500,
  `transactions`.`winnings` = transactions.winnings + (entries.amount * 500)

Error message given by SQLite:

Result: near "inner": syntax error
At line 1:
update
  `bets`
  inner

asked Jun 27 '19 at 19:14

blacklotus

1

SQLite does not support joins in an UPDATE statement. – forpas Jun 27 '19 at 19:19
1

See https://www.sqlite.org/lang_update.html for sqlite's update syntax. That... isn't close to anything it understands. – Shawn Jun 27 '19 at 19:20
Thanks. So that's why hmmm.... – blacklotus Jun 27 '19 at 19:28
Duplicate of: https://stackoverflow.com/questions/773441/how-do-i-make-an-update-while-joining-tables-on-sqlite – Barmar Jun 27 '19 at 19:31

Cross table update join operation works in MySQL but not SQLite

0 Answers0