要更新：

UPDATE table
SET type = 3
WHERE id IN (
  SELECT id 
  FROM table t1 
  JOIN table t2 
   ON t1.id = t2.id 
   AND t1.type = 3 
   AND t2.type = 4
) ids

要选择：

SELECT
id,
IF(t.type = 4 AND (IFNULL(ids.id, 0) > 0),3,t.type) as type
FROM table t
LEFT JOIN (
      SELECT id 
      FROM table t1 
      JOIN table t2 
       ON t1.id = t2.id 
       AND t1.type = 3 
       AND t2.type = 4
    ) ids
ON ids.id = t.id

我有一个，这是很多步骤，但至少是工作。你可以自己简单化。

CREATE TABLE order_items (no INT AUTO_INCREMENT PRIMARY KEY, id INT, type INT);

INSERT INTO order_items(id, type) VALUES(1, 1) ,(1, 2),(1, 3),(1, 4),(2, 1), (3, 4), (4, 1), (4, 4);

SELECT * FROM order_items;

WITH cte AS (
    SELECT no FROM order_items WHERE id IN (SELECT a.id FROM order_items a, (SELECT NO, id FROM order_items WHERE TYPE = 4) b WHERE a.id = b.id  GROUP BY id HAVING COUNT(a.id) > 1) AND TYPE = 4
)
UPDATE order_items
SET order_items.type = 3
WHERE order_items.no IN (SELECT NO FROM cte);

SELECT * FROM order_items;