SQL查询用于更新

Question

我正在尝试将这个SELECT语句转换为UPDATE语句，但是太难了。我甚至不知道在更新时是否可以保留INNER JOIN，所以我想从SQL天才那里寻求帮助。

我需要在liste_objectif表中将客户ID更新为NULL，并且我在客户表中有此ID（通过电子邮件）。

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

谢谢！

- DERET Pierre-Yves

---

你使用的是哪种数据库管理系统？ - jarlh

你想要更新什么？你想将其设置为什么？到目前为止你尝试了什么？你使用的是哪种实际的 SQL 版本？ - BishNaboB

我应该说抱歉，我正在使用MYSQL。 - DERET Pierre-Yves

谢谢@Kaiser，这真的很有帮助！ - DERET Pierre-Yves

@DERETPierre-Yves 欢迎您 - Kaiser

Answer 1

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

- Prasanna Kumar J

Answer 2

你可以这样使用JOIN更新（我不知道你要设置在liste_objectifs中的列的名称，所以我将其命名为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
  )

- ScaisEdge

---

感谢您的回答！ - DERET Pierre-Yves

Answer 3

这个很接近：

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;

如果你确实需要使用order by和limit，那么查询将需要更加复杂。这些在多表更新中不被支持。

- Gordon Linoff

---

谢谢，这就是答案！ - DERET Pierre-Yves