Question 20270 in Mysql Question posted in
The official MySQL documentation can be found here.

Select SQL for Modifying the output value based on some conditions in same table – Mysql

Given the following table:

 ... |  id    | type | ...
 ... |    1   |  1   | ...
 ... |    1   |  2   | ...
 ... |    1   |  3   | ...
 ... |    1   |  4   | ...
 ... |    2   |  1   | ...
 ... |    2   |  2   | ...
 ... |    3   |  1   | ...
 ... |    3   |  3   | ...
 ... |    4   |  1   | ...
 ... |    5   |  4   | ...

I want to write a select query that will return rows
and change the return value to from 4 to 3 if (the same id and type 3 & 4) existed.

Noted that if only type = ‘4’ and without the (same id & type = ‘3’) existed,
the result will not be modified.

Example query that doesn’t work as it can’t check the upper conditions:

SELECT id,
CASE WHEN (type = ‘4’)
THEN ‘3’
ELSE type end AS type
FROM table;

My desired output would be:

 ... |  id    | type | ...
 ... |    1   |  1   | ...
 ... |    1   |  2   | ...
 ... |    1   |  3   | ...
 ... |    1   |  3*  | ...
 ... |    2   |  1   | ...
 ... |    2   |  2   | ...
 ... |    3   |  1   | ...
 ... |    3   |  3   | ...
 ... |    4   |  1   | ...
 ... |    5   |  4   | ...
Tags:

2

Answers


  1. 0

    To update:

    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

    To select:

    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
    Login or Signup to reply.
  2. 0

    I have one, this is to many step but at least is work. You can simplefy it by your self.

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search