How can I perform this MySQL update command

Question

My SQL (no pun intended) is rather rusty. I need to update user's group_id in Table A based on a combination of data from Tables B & C. Can someone give me some pointers on how I should do it.

Here is how the SELECT statement looks:

SELECT group_id
    FROM exp_channel_data d, exp_channel_titles t, exp_members m
    WHERE d.field_id_19 LIKE  '%[362]%'
    AND t.entry_id = d.entry_id
    AND t.author_id = m.member_id

1

Run an `UPDATE` statement? – PM 77-1 Sep 11 '14 at 01:53

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

At the moment, I'm more familiar with MSSQL, but I believe this will also work in MySQL. You haven't indicated what you want to update group_id to, so I just inserted a random string in there. If you want to set it to the value from another table in the query, you can also do that by doing something like m.group_id = d.group_id:

UPDATE m SET
    m.group_id = 'newValue'
FROM
    exp_members m
    INNER JOIN exp_channel_titles t
        ON m.member_id = t.author_id
    INNER JOIN exp_channel-data d
        ON t.entry_id = d.entry_id
        AND d.field_id_19 LIKE '%[362]%'

As you can see, I've changed your implicit joins to explicit ones (eg INNER JOIN). I really recommend this syntax, as it's easier to see what's going on by separating your WHERE conditions from your JOIN conditions.

Update:

It looks like MySQL doesn't support the UPDATE...FROM syntax used above. Try this instead:

UPDATE
    exp_members m,
    exp_channel_titles t,
    exp_channel_data d
SET
    m.group_id = 'newValue'
WHERE
    m.member_id = t.author_id
    AND t.entry_id = d.entry_id
    AND d.field_id_19 LIKE '%[362]%'

Again, this isn't tested, but I think it will work. If not, it might at least help you get you closer to your answer.

HI Travesty3, thanks for your post. I ran the query but it's throwing the following error : #1064 - 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 exp_members m INNER JOIN exp_channel_titles t ON m.membe' at line 3. Any thoughts? — KoalaKid, Sep 11 '14 at 22:11
@user1684180: Hmm, it looks like MySQL does not allow `UPDATE...FROM` syntax. So it looks like it might have to be done with an implicit join, as you had before. I will update my answer. — Travesty3, Sep 12 '14 at 15:01

Fabio Duran Verdugo · Answer 2 · 2014-09-11T01:57:57.257

-1

Maybe UPDATE from SELECT solve you problem...

 UPDATE TABLE 
 SET group_id = (SELECT group_id FROM ...)
 WHERE group_id = <your_criteria>

edited Sep 11 '14 at 01:57

answered Sep 11 '14 at 01:52

Fabio Duran Verdugo

172
1
5

How can I perform this MySQL update command

2 Answers2

Update: