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 — hjpotter92
– hjpotter92, Commented Mar 8, 2013 at 14:58

John Woo · Accepted Answer · 2013-03-08 14:56:41Z

2

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'

answered Mar 8, 2013 at 14:56

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

hjpotter92 · Accepted Answer · 2013-03-08 14:57:48Z

0

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'

answered Mar 8, 2013 at 14:57

hjpotter92

81.1k36 gold badges148 silver badges188 bronze badges

Comments

Umair Saleem · Accepted Answer · 2013-03-08 14:57:58Z

0

You need to do something like

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

answered Mar 8, 2013 at 14:57

Umair Saleem

1,0635 silver badges19 bronze badges

Comments

Jeremy Farrell · Accepted Answer · 2013-03-08 15:55:22Z

0

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.

edited Mar 8, 2013 at 15:55

answered Mar 8, 2013 at 15:05

Jeremy Farrell

1,49115 silver badges16 bronze badges

Collectives™ on Stack Overflow

Converting mysql select query with joins to update query with joins

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related