Converting mysql select query with joins to update query with joins

Question

I have a select query like this:

SELECT n.nid, n.title, n2.nid, n2.title, n2.type
FROM node n
    JOIN og_ancestry o ON n.nid = o.group_nid
    JOIN node n2 ON n2.nid = o.nid 
    JOIN og_access_post a ON o.nid = a.nid
    JOIN content_type_group c ON n.vid = c.vid 
WHERE n.type = 'group'
    AND a.og_public = 1
    AND c.field_tyyppi_value != 'type1'

I can't figure out how to convert this into update query. I tried like this:

UPDATE og_access_post a
    SET a.og_public = 0
FROM node n
    JOIN og_ancestry o ON n.nid = o.group_nid
    JOIN node n2 ON n2.nid = o.nid 
    JOIN og_access_post a ON o.nid = a.nid
    JOIN content_type_group c ON n.vid = c.vid 
WHERE n.type = 'group'
    AND a.og_public = 1
    AND c.field_tyyppi_value != 'type1'

But I am getting an 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 n.nid, n.title, n2.nid

I have found some solutions but with those I would need to modify the order of my tables and joins. The logic in the select query is right so it makes no sense to me that in order to do update I have to think the logic of the query through again.

How can I convert my query for updating without changing the structure of the query fundamentally?

possible duplicate of [UPDATE multiple tables in MySQL using LEFT JOIN](http://stackoverflow.com/questions/806882/update-multiple-tables-in-mysql-using-left-join) — hjpotter92, Mar 08 '13 at 14:58

score 2 · Accepted Answer · answered Mar 08 '13 at 14:56

UPDATE with join in MYSQL doesn't use FROM clause,

update  og_access_post a 
        JOIN og_ancestry o ON o.nid = a.nid
        JOIN node n ON n.nid = o.group_nid
        JOIN node n2 ON n2.nid = o.nid
        JOIN content_type_group c ON n.vid = c.vid 
SET     a.og_public = 0 
WHERE   n.type = 'group' AND 
        a.og_public = 1 AND 
        c.field_tyyppi_value != 'type1'

score 0 · Answer 2 · answered Mar 08 '13 at 14:57

UPDATE og_access_post a
JOIN og_ancestry o 
    ON o.nid = a.nid
JOIN node n2 
    ON n2.nid = o.nid 
JOIN content_type_group c 
    ON n.vid = c.vid 
SET a.og_public = 0
WHERE n.type = 'group' 
    AND a.og_public = 1 
    AND c.field_tyyppi_value != 'type1'

score 0 · Answer 3 · answered Mar 08 '13 at 14:57

0

You need to do something like

 update tableA set someColumn = 
        (SELECT someColumn 
            FROM tableA a
            where id=x
         )

answered Mar 08 '13 at 14:57

Umair Saleem

1,055
5
19

Jeremy Farrell · Answer 4 · 2013-03-08T15:55:22.680

One way of doing this would be:

UPDATE og_access_post a SET og_public = 0 
WHERE a.{PRIMARY_KEY} IN (SELECT a.{PRIMARY_KEY} FROM 
    node n JOIN og_ancestry o ON n.nid = o.group_nid
    JOIN node n2 ON n2.nid = o.nid 
    JOIN og_access_post a ON o.nid = a.nid
    JOIN content_type_group c ON n.vid = c.vid 
    WHERE n.type = 'group' AND 
    a.og_public = 1 AND c.field_tyyppi_value != 'type1')

I used a.{PRIMARY_KEY} because I couldn't tell what your primary key was.

Converting mysql select query with joins to update query with joins

4 Answers4