CODEWITHSUNDEEP
mysqlsqlsql-update
jjei
jjei

Reputation: 1290

Converting mysql select query with joins to update query with joins

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?

Upvotes: 1

Views: 243

Answers (4)

Jeremy Farrell
Jeremy Farrell

Reputation: 1491

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.

Upvotes: 0

Umair Saleem
Umair Saleem

Reputation: 1063

You need to do something like

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

Upvotes: 0

hjpotter92
hjpotter92

Reputation: 80649

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'

Upvotes: 0

John Woo
John Woo

Reputation: 263843

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'

Upvotes: 2

Related Questions