SQL Select to Update

Question

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 JOINs 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 !

What are you trying to update? What are you trying to set it to? What have you tried so far? Which actual flavour of SQL are you using? — BishNaboB, Nov 22 '16 at 15:22
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) — DERET Pierre-Yves, Nov 22 '16 at 15:24
look at this http://stackoverflow.com/questions/14618703/update-query-using-subquery-in-sql-server — Kaiser, Nov 22 '16 at 15:24

score 1 · Answer 1 · answered Nov 22 '16 at 15:29

Update liste_objectifs l
INNER JOIN customers c ON ( l.email = c.customer_email          )
  set l.customer_id=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
)
and customer_id is null

score 1 · Answer 2 · answered Nov 22 '16 at 15:30

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
  )

Thanks you for your answer! – DERET Pierre-Yves Nov 22 '16 at 15:41 — DERET Pierre-Yves, Nov 22 '16 at 15:41

score 1 · Accepted Answer · answered Nov 22 '16 at 15:31

This comes close:

update liste_objectifs l join
       customers c 
       on l.email = c.customer_email
    set l.customer_id = c.customer_id
where c.customer_id (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
                           ) and
      l.customer_id is null;

If you actually need order by and limit, then the query will need to be more complicated. Those are not supported for multi-table updates.

Thanks this was the answer! – DERET Pierre-Yves Nov 22 '16 at 15:41 — DERET Pierre-Yves, Nov 22 '16 at 15:41

SQL Select to Update

3 Answers3