Converting a mysql syntax query into sqlite (3)

Question

I have the following query:

Update articles a
inner join 
(
  select article_id, amount_required, amount_sold from products_articles,sales where 
  sales.product_id = products_articles.product_id and sales.id = '4c6d35bf-994a-4689-a32e-5d8196b24b06'
) b on a.id=b.article_id
set a.amount_in_stock=a.amount_in_stock-(amount_required*amount_sold)

And while it works well in mysql (or mariadb), I'm getting an error running this in sqlite:

near "a": syntax error

Does not sqlite support aliases or is it they way they are used?

How would I go about converting this syntax?

Does this answer your question? [Update with Join in SQLite](https://stackoverflow.com/questions/19270259/update-with-join-in-sqlite) — nbk, Mar 08 '21 at 18:56
That was a great post, but using that I could still not draft a working sql query — Yonder, Mar 08 '21 at 19:55

forpas · Answer 1 · 2021-03-08T20:17:24.453

0

SQLite does not support joins in the UPDATE statement.

From version 3.33.0 the UPDATE FROM syntax is supported, so you can do this:

with cte as (
  select article_id, amount_required, amount_sold 
  from products_articles pa inner join sales s 
  on s.product_id = pa.product_id 
  where s.id = '4c6d35bf-994a-4689-a32e-5d8196b24b06'
) 
update articles as a
set amount_in_stock = a.amount_in_stock - (c.amount_required * c.amount_sold)
from cte as c  
where c.article_id = a.id

In prior versions you need a correlated subquery:

update articles
set amount_in_stock = amount_in_stock - coalesce(
  (
    select amount_required * amount_sold 
    from products_articles pa inner join sales s 
    on s.product_id = pa.product_id 
    where s.id = '4c6d35bf-994a-4689-a32e-5d8196b24b06'
      and article_id = a.id
 ), 0)

edited Mar 08 '21 at 20:17

answered Mar 08 '21 at 19:43

forpas

160,666
10
38
76

Thanks a lot for your two options, I'm using sqlite 3 (v5). I'm getting near \".\": syntax error" on both of these though, and I can't seem to figure out where – Yonder Mar 08 '21 at 19:56
@Yonder There is no v5 version. Execute `SELECT sqlite_version()` to get SQLite's version. I edited. The syntax is now correct, check this: https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=48ceff4f416ca7b8427a6be6c6ec90f0 – forpas Mar 08 '21 at 20:12
@Yonder a correlated subquery in the UPDATE statement will have worse performance than the UPDATE FROM syntax. So if it's possible upgrade SQLite to the latest version. – forpas Mar 08 '21 at 20:18

score 0 · Answer 2 · edited May 04 '21 at 22:03

0

CREATE tABLE articles (id int, amount_in_stock int)

CREATE tABLE products_articles (product_id INT,article_id int)

CREATE tABLE sales (id int,product_id int,amount_required int, amount_sold int)

update articles as a
set amount_in_stock = amount_in_stock - coalesce(
  (
    select amount_required * amount_sold 
    from products_articles pa inner join sales s 
    on s.product_id = pa.product_id 
    where s.id = '4c6d35bf-994a-4689-a32e-5d8196b24b06'
      and article_id = a.id
 ), 0)

db<>fiddle here

edited May 04 '21 at 22:03

Makoto

104,088
27
192
230

answered Mar 08 '21 at 20:09

nbk

45,398
8
30
47

Wow, you didn't even change the formatting of my code. – forpas Mar 08 '21 at 20:25

Converting a mysql syntax query into sqlite (3)

2 Answers2