Sql Update with Join statement

Question

I have two tables : first one stores main informations and the second stores some extra informations. I need to update the first table with the help of some data stored at second table.

My SELECT statement is working

SELECT  news.news_id, 
        news.title, 
        news.cat_id, 
        news.sub_cat_id, 
        news_extra.date_vision_tr
FROM news_extra
    JOIN news 
        ON news.news_id = news_extra.news_id
WHERE news.cat_id=1 AND sub_cat_id=5 AND news_extra.date_vision_tr < CURDATE()

Than I tried UPDATE statement like this

UPDATE news SET news.sub_cat_id=8 
FROM news
   INNER JOIN news_extra
        ON news.news_id = news_extra.news_id
WHERE news.cat_id=1 AND sub_cat_id=5 AND news_extra.date_vision_tr < CURDATE()

But its not working. Giving this error

*You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM news INNER JOIN news_extra ON news.news_id = news_extra.news_id WHERE news.' at line 2*

score 1 · Accepted Answer · answered Feb 19 '13 at 09:01

The syntax you are using is for SQL Server.

Here is the syntax for MySQL

UPDATE  news a
        INNER JOIN news_extra b
            ON a.news_id = b.news_id
SET     a.sub_cat_id = 8 
WHERE   a.cat_id = 1 and 
        sub_cat_id = 5 and 
        b.date_vision_tr < CURDATE()

score 0 · Answer 2 · edited May 23 '17 at 10:32

0

You must be familiar with SQL Server more. The syntax you are using will work in SQLServer. For MySQL:

UPDATE news
INNER JOIN news_extra
ON news.news_id = news_extra.news_id
SET news.sub_cat_id=8 
WHERE news.cat_id=1 and sub_cat_id=5 and news_extra.date_vision_tr < CURDATE()

--Try this way.

Check similar thread

edited May 23 '17 at 10:32

Community

1
1

answered Feb 19 '13 at 09:04

Mayukh Roy

1,815
3
19
31

Sql Update with Join statement

2 Answers2