MySQL subquery based on other row

Question

I have a table that looks like this:

entity_guid    | name           | value
---------------|----------------|--------
189501         | context        | groups
189501         | column         | 3
189509         | context        | profile
189509         | column         | 3
189521         | context        | profile
189521         | column         | 2
189551         | context        | groups
189551         | column         | 1
189552         | context        | groups
189552         | column         | 3
189554         | context        | groups
189554         | column         | 2
189559         | context        | profile
189559         | column         | 1
189591         | context        | profile
189591         | column         | 3

I want to update the rows where name = column and value = 3 so that value = 2. But I only want to update the rows where another row exists with the same entity_guid and with name = context and value = groups.

So for example, if I were to select the rows I want to change, they would be:

entity_guid    | name           | value
---------------|----------------|--------
189501         | column         | 3
189552         | column         | 3

I have tried:

SELECT * FROM (
    SELECT * FROM `elggprivate_settings` WHERE `name` = 'context' AND `value` = 'groups'
) AS subquery
WHERE `name` = 'column' AND `value` = 3

But that of course does not work because the table is not set up like that; there are multiple rows for the same entity_guid. By the way, this is a table in a database for elgg, the social network engine. I am not very good with MySQL and could not find an answer online.

Answer 1

xQbert was close, but he forgot the context/groups clause for checking the subrecord existence.

UPDATE 
  elggprivate_settings
SET
   value = '2'
WHERE
   name = 'column' AND value = '3'
   AND entity_guid IN (
     SELECT 
       entity_guid 
     FROM 
       elggprivate_settings 
     WHERE 
       name = 'context'  
       AND value = 'groups')