CODEWITHSUNDEEP
mysqlsqljoin
walkerspace
walkerspace

Reputation: 1

MySQL Join Update

A (supposedly) simple update has turned out to be not so simple. No matter which syntax I try from other answered questions, I get the same response from MySQL:

0 row(s) affected Rows matched: 124 Changed: 0 Warnings: 0

Try 1:

UPDATE news_content, news_map
SET news_content.active='no'
WHERE news_content.rowID = news_map.newsID
AND news_map.catID = 170;

Try 2:

UPDATE  news_content
LEFT JOIN
    news_map
ON      news_map.newsID = news_content.rowID
SET     news_content.active = 'no'
WHERE   news_map.catID = 170;

Try 3:

UPDATE 
news_content nc JOIN
news_map nm 
  ON nm.newsID = nc.rowID 
  AND nm.catID = 170
SET nc.active = 'no';

What do you think will work?

Upvotes: 0

Views: 81

Answers (3)

MatheusOl
MatheusOl

Reputation: 11815

Try this:

UPDATE news_content
SET active='no'
WHERE rowID IN (SELECT m.newsID FROM news_map m WHERE m.catID = 170);

I prefer to use sub-query instead of JOIN on UPDATE, in general, as it is a more standard way.

Upvotes: 0

dwjv
dwjv

Reputation: 1227

Might be a silly question but does news_content.active already equal 'no'?

Upvotes: 1

shibormot
shibormot

Reputation: 1638

Are there any rows returned by this:

select *
from news_content
LEFT JOIN
    news_map
ON      news_map.newsID = news_content.rowID
WHERE   news_map.catID = 170

Upvotes: 0

Related Questions