SQL query updating column in a table based on comparing two different pairs of value from different-CodePudding

I have two tables: TABLE_1 and TABLE_2

The query should compare one pair of values in two colums in one table and corresponding pair of values in two rows in another table.

I need to update whole column RESULT with values 1 OR 0 this way:

IF (PRICE_LEFT > PRICE_RIGHT AND COL_LEFT_RANK > COL_RIGHT_RANK)
OR (PRICE_LEFT < PRICE_RIGHT AND COL_LEFT_RANK < COL_RIGHT_RANK)
THEN RESULT = 1
ELSE RESULT = 0

I used imaginary COL_LEFT_RANK and COL_LEFT_RANK as columns with assigned values from RANK column corresponding to their values in COL_LEFT and COL_RIGHT

I am using SQL Server LocalDB database.

Here is the example of how it should work:

Here's DDL:

CREATE TABLE TABLE_1 (
    COL_LEFT varchar(255),
    COL_RIGHT varchar(255),
    PRICE_LEFT int,
    PRICE_RIGHT int
);

CREATE TABLE TABLE_2 (
    COL varchar(255),
    RANK int
);


INSERT INTO TABLE_1 (COL_LEFT, COL_RIGHT, PRICE_LEFT, PRICE_RIGHT) VALUES ('B', 'G', 22, 4);
INSERT INTO TABLE_1 (COL_LEFT, COL_RIGHT, PRICE_LEFT, PRICE_RIGHT) VALUES ('C', 'A', 15, 14);
INSERT INTO TABLE_1 (COL_LEFT, COL_RIGHT, PRICE_LEFT, PRICE_RIGHT) VALUES ('B', 'D', 5, 18);
INSERT INTO TABLE_1 (COL_LEFT, COL_RIGHT, PRICE_LEFT, PRICE_RIGHT) VALUES ('A', 'F', 2, 2);
INSERT INTO TABLE_1 (COL_LEFT, COL_RIGHT, PRICE_LEFT, PRICE_RIGHT) VALUES ('F', 'E', 4, 8);
INSERT INTO TABLE_1 (COL_LEFT, COL_RIGHT, PRICE_LEFT, PRICE_RIGHT) VALUES ('G', 'C', 16, 6);
INSERT INTO TABLE_1 (COL_LEFT, COL_RIGHT, PRICE_LEFT, PRICE_RIGHT) VALUES ('D', 'C', 22, 28);
INSERT INTO TABLE_1 (COL_LEFT, COL_RIGHT, PRICE_LEFT, PRICE_RIGHT) VALUES ('A', 'G', 14, 19);
INSERT INTO TABLE_1 (COL_LEFT, COL_RIGHT, PRICE_LEFT, PRICE_RIGHT) VALUES ('F', 'D', 3, 12);
INSERT INTO TABLE_1 (COL_LEFT, COL_RIGHT, PRICE_LEFT, PRICE_RIGHT) VALUES ('B', 'A', 11, 9);
INSERT INTO TABLE_1 (COL_LEFT, COL_RIGHT, PRICE_LEFT, PRICE_RIGHT) VALUES ('D', 'F', 8, 2);
INSERT INTO TABLE_1 (COL_LEFT, COL_RIGHT, PRICE_LEFT, PRICE_RIGHT) VALUES ('B', 'F', 4, 1);

INSERT INTO TABLE_2 (COL, RANK) VALUES ('A', 5);
INSERT INTO TABLE_2 (COL, RANK) VALUES ('B', 3);
INSERT INTO TABLE_2 (COL, RANK) VALUES ('C', 1);
INSERT INTO TABLE_2 (COL, RANK) VALUES ('D', 7);
INSERT INTO TABLE_2 (COL, RANK) VALUES ('E', 6);
INSERT INTO TABLE_2 (COL, RANK) VALUES ('F', 2);
INSERT INTO TABLE_2 (COL, RANK) VALUES ('G', 4);

CodePudding user response：

here you go

select COL_LEFT,tb21.[RANK],COL_RIGHT,tb22.[RANK],PRICE_LEFT, PRICE_RIGHT, 
result = case 
            when (tb21.[RANK] > tb22.[RANK] and PRICE_LEFT > PRICE_RIGHT) or (tb21.[RANK] < tb22.[RANK] and PRICE_LEFT < PRICE_RIGHT)
            then 1
            else 0
        end
from table_1 tb1
left join table_2 tb21 on tb1.COL_LEFT = tb21.col
left join TABLE_2 tb22 on tb1.COL_RIGHT = tb22.col;

using update

update table_1
set table_1.result = case 
            when (tb21.[RANK] > tb22.[RANK] and PRICE_LEFT > PRICE_RIGHT) or (tb21.[RANK] < tb22.[RANK] and PRICE_LEFT < PRICE_RIGHT)
            then 1
            else 0
        end
from table_1 tb1
left join table_2 tb21 on tb1.COL_LEFT = tb21.col
left join TABLE_2 tb22 on tb1.COL_RIGHT = tb22.col;