SQL Update Table 1 when Table 2 contains a specific value-CodePudding

I want to update lasstock of table s_articles to 1 when laststock is 0 and is either in categoryID 242, 243 or 244 in table s_articles_categories_ro.

I came up with this SQL, which doesn't work.

UPDATE a
SET a.laststock = 1
FROM s_articles AS a
LEFT JOIN `s_articles_categories_ro` AS ac
ON a.id = ac.articleID
WHERE a.laststock=0 AND ac.categoryID IN (242,243,244)

I have these 2 tables:
s_articles

id	laststock
1	0
2	1
3	0
4	0

s_articles_categories_ro

id	articleID	categoryID
1	1	242
2	1	12
3	1	8
4	2	2
5	3	8
6	4	21
7	1	244

CodePudding user response：

You should be able to use exists correlated query here

update s_articles a
set laststock = 1
where laststock = 0
 and exists (
   select * from s_articles_categories_ro ac 
     where ec.categoryId in (242,243,244) 
       and ac.articleId = a.Id
   );

CodePudding user response：

UPDATE statement might need to use JOIN before SET in MySQL, and I think we might use JOIN instead of OUTER JOIN

UPDATE s_articles a
INNER JOIN `s_articles_categories_ro` AS ac
ON a.id = ac.articleID
SET a.laststock = 1
WHERE a.laststock = 0 AND ac.categoryID IN (242,243,244)

CodePudding user response：

why do you use "left join"?

Try this:

UPDATE s_articles
JOIN  s_articles_categories_ro ON s_articles.id = s_articles_categories_ro.articleID 
SET laststock = 1
WHERE s_articles.laststock=0 AND s_articles.categoryID IN (242,243,244)

Another option

Try this:

UPDATE s_articles SET laststock = 1
Where id in (
SELECT id from s_articles
JOIN  s_articles_categories_ro ON s_articles.id = s_articles_categories_ro.articleID 
WHERE s_articles.laststock=0 AND s_articles.categoryID IN (242,243,244)
)