q43105677, MySQL - rextester

Language: Layout:

DROP TABLE IF EXISTS orderlines, purchases;

CREATE TABLE orderlines(id INT PRIMARY KEY, col1 VARCHAR(20), col2 VARCHAR(20), col3 VARCHAR(20));
CREATE TABLE purchases(id INT, status_id INT);

INSERT INTO orderlines VALUES
(1, "1 - col1", "1 - col2", "1 - col3"),
(2, "2 - col1", "2 - col2", "2 - col3"),
(3, "3 - col1", "3 - col2", "3 - col3"),
(4, "4 - col1", "4 - col2", "4 - col3"),
(5, "5 - col1", "5 - col2", "5 - col3");

INSERT INTO purchases VALUES
(1, 0),
(2, 1);

SELECT * FROM orderlines;
SELECT * FROM purchases;

-- ------------------------------------------------------------------------------------------------------
-- insert into orderlines(id, col1, col2)
-- values
-- (1, "1 - col1 - updated", "1 - col2 - updated")
-- ON DUPLICATE KEY
-- UPDATE
-- orderlines INNER JOIN purchases
-- ON orderlines.id = purchases.id
-- SET orderlines.col3 = "truevalue" WHERE purchases.status_id = 0;

-- ------------------------------------------------------------------------------------------------------

SELECT CASE 
        WHEN purchases.status_id = 0 THEN "truevalue" 
        ELSE "falsevalue" 
       END AS title 
FROM purchases WHERE id = 2;

SELECT IF(
            (SELECT CASE 
                        WHEN purchases.status_id = 0 THEN "truevalue" 
                    ELSE "falsevalue" 
                END AS title 
                FROM purchases WHERE id = 2
              ) = "truevalue", "x", "y"
       );

-- insert into orderlines (id, col1, col2)
-- values(1, "1 - col1 - updated", "1 - col2 - updated")
-- ON DUPLICATE KEY
-- UPDATE
-- col3 = (SELECT CASE WHEN purchases.status_id = 0 "truevalue" ELSE col3 FROM purchases where id = orderlines.id);

INSERT INTO orderlines (id, col1, col2)
    VALUES (1, "1 - col1 - updated", "1 - col2 - updated")
    ON DUPLICATE KEY
    UPDATE 
         col3 = IF(
                     (SELECT CASE 
                                WHEN purchases.status_id = 0 THEN "truevalue" 
                             ELSE "falsevalue" 
                      END AS title 
                      FROM purchases WHERE id = 1
                     ) = "truevalue", 
                     "truevalue", 
                     col3
                 );

INSERT INTO orderlines (id, col1, col2)
    VALUES (2, "2 - col1 - updated", "2 - col2 - updated")
    ON DUPLICATE KEY
    UPDATE 
         col3 = IF(
                     (SELECT CASE 
                                WHEN purchases.status_id = 0 THEN "truevalue" 
                             ELSE "falsevalue" 
                      END AS title 
                      FROM purchases WHERE id = 2
                     ) = "truevalue", 
                     "truevalue", 
                     col3
                 );

SELECT * FROM orderlines;

-- ------------------------------------------------------------------------------------------------------
DROP TABLE orderlines, purchases;


absolute service time: 0,44 sec