MySQL – Update Based on SELECT Result

MySQLsubqueryupdate

Here are two SQL statements. One is an update statement and the other is a select statement. I want to combine them.

Update

update customer_entity 
set website_id = 4 
where website_id = 3

Select

SELECT count(*) c, email 
FROM `customer_entity` 
where website_id in (3,4) 
group by email 
having c <= 1) e

I tried:

update customer_entity 
set website_id = 4 
where website_id = 3 
where email in 
(
    SELECT count(*) c, email 
    FROM `customer_entity` 
    where website_id in (3,4) 
    group by email 
    having c <= 1
) e)

I want to update the website_id according to the select statement. I tried to implement it but not successful, does anyone know how to do that?

Best Answer

update customer_entity 
set website_id = 4             
where website_id = 3 
AND email in 
(
    select tt.email 
    from 
    (
        SELECT email 
        FROM `customer_entity` 
        where website_id in (3,4)
        group by email 
        having count(email) <= 1
    ) as tt
)

Related Solutions

MySQL Subquery – How to Solve Correlated Subquery Problems

Your query should be like:

SELECT FIRST_NAME, MIN_EMP_SALARY FROM
(
    SELECT  FIRST_NAME , (SELECT MIN(SALARY) 
                         FROM EMPLOYEES EMP
                         WHERE E.EMPLOYEE_ID = EMP.EMPLOYEE_ID
                         GROUP BY EMPLOYEE_ID) AS MIN_EMP_SALARY
    FROM  EMPLOYEES E
    GROUP BY FIRST_NAME , EMPLOYEE_ID
) OUTER_TABLE WHERE MIN_EMP_SALARY >= 6000
ORDER BY MIN_EMP_SALARY DESC

MySQL update one table from another joined many-to-many

This works - I had the JOIN wrong

UPDATE `events` 
  INNER JOIN `plays_in_events` ON (`plays_in_events`.`EventID` = `events`.`EventID`)
  INNER JOIN `plays` ON (`plays_in_events`.`PlayID` = `plays`.`PlayID`)
  SET events.Name = plays.Play
WHERE 
  events.Name IS NULL and plays_in_events.PlayID = 1

and the earlier comments were quite right, but it took me a while to 'get it' - thanks for your help. I had just copied the JOIN lines from the working SELECT query, but when I looked again, I could finally see what the commentators meant about events appearing twice, and plays_in_events not appearing properly in the JOINs.