DERET Pierre-Yves DERET Pierre-Yves - 11 days ago 6
MySQL Question

SQL Select to Update

I'm trying to convert this

SELECT
statement to an
UPDATE
statement but it's too hard. I don't even know if I can keep
INNER JOIN
s while updating, so I think I need some help from an SQL genius.

I need to update my customer ID on liste_objectif where it's NULL, and I have this id on customers table (by the mail)

SELECT DISTINCT *
FROM liste_objectifs l
INNER JOIN customers c ON ( l.email = c.customer_email )
WHERE c.customer_id
IN (
SELECT customer
FROM newsletters_inscriptions
WHERE liste
IN ( 786, 878, 874, 875, 876, 877 )
)
AND c.customer_id NOT
IN (
SELECT customer
FROM newsletters_blacklists
WHERE newsletter =1
)
ORDER BY `l`.`email` ASC
LIMIT 0 , 30


Thanks !

Answer

You can use JOIN in update this way ( i don't know the name of you column to set in liste_objectifs so i named it your_customer_id_column)

  UPDATE liste_objectifs l
  INNER JOIN FROM customers c   ON l.email = c.customer_email  
  SET  liste_objectifs.your_customer_id_column  = c.id
  WHERE c.customer_id
  IN (
      SELECT customer
      FROM newsletters_inscriptions
      WHERE liste
      IN ( 786, 878, 874, 875, 876, 877 )
  )
  AND c.customer_id NOT 
  IN (
      SELECT customer
      FROM newsletters_blacklists
      WHERE newsletter =1
  )
Comments