How to update every single row of a column based on a condition in another table-CodePudding

I have a table A

aId    aCode
1      ABC
2      DEF
3      GHI

..and B table

bId    bCode
1      JKL
2      MNO
3      PQR

In a C table I should update a column 'inAorB' if the cCode is in A or B table so it look like this:

cId    cCode inAorB
1      ABC    A
2      GHI    A
3      PQR    B

.. right now C looks like this:

cId    cCode inAorB
1      ABC    NULL
2      GHI    NULL
3      PQR    NULL

It should update all rows.

CodePudding user response：

To reproduce your code, you can do like so :

CREATE TABLE #A
(
    [aId] INT,
    [aCode] VARCHAR(MAX)
)

INSERT INTO #A VALUES
    (1, 'ABC'),
    (2, 'DEF'),
    (3, 'GHI')

CREATE TABLE #B
(
    [bId] INT,
    [bCode] VARCHAR(MAX)
)

INSERT INTO #B VALUES
    (1, 'JKL'),
    (2, 'MNO'),
    (3, 'PQL')

CREATE TABLE #C
(
    [cId] INT,
    [cCode] VARCHAR(MAX),
    [inAorB] CHAR(1)
)

INSERT INTO #C VALUES
    (1, 'ABC',NULL),
    (2, 'GHI',NULL),
    (3, 'PQR',NULL)

-- The script you are interested in
UPDATE C
SET [inAorB] = CASE
                    WHEN A.aId IS NOT NULL THEN 'A'
                    WHEN B.bId IS NOT NULL THEN 'B'
                    ELSE NULL
                END
FROM #C AS C
LEFT JOIN #A AS A ON A.aCode = C.cCode
LEFT JOIN #B AS B ON B.bCode = C.cCode
-- END

SELECT * FROM #C

DROP TABLE #A
DROP TABLE #B
DROP TABLE #C

If both are not null, 'A' will be set

CodePudding user response：

You can use a CASE expression to apply a specific outcome if the condition is true. Note that if a code can appear in both A and B, this will pick A.

UPDATE C SET inAorB = CASE
  WHEN EXISTS (SELECT 1 FROM dbo.A WHERE A.aCode = C.cCode) THEN 'A' 
  WHEN EXISTS (SELECT 1 FROM dbo.B WHERE B.bCode = C.cCode) THEN 'B' 
  ELSE '?' -- in case of no match
END
FROM dbo.C AS C;

Working example in this fiddle. Note that abstracting to EXISTS will avoid unnecessary sorts you'll find with LEFT JOINs.

CodePudding user response：

--A TABLE
DECLARE @A_Table AS TABLE
(
    aId INT,
    aCode VARCHAR(100)
);

--INSERTING TO A TABLE
INSERT @A_Table
(
    aId,
    aCode
)
VALUES
(1, 'ABC'),
(2, 'DEF'),
(3, 'GHI');


--B TABLE
DECLARE @B_Table AS TABLE
(
    bId INT,
    bCode VARCHAR(100)
);

--INSERTING TO B TABLE
INSERT @B_Table
(
    bId,
    bCode
)
VALUES
(1, 'JKL'),
(2, 'MNO'),
(3, 'PQR');

--C TABLE
DECLARE @C_Table AS TABLE
(
    cId INT,
    cCode VARCHAR(100),
    inAorB VARCHAR(100)
);

--INSETING TO C TABLE
INSERT @C_Table
(
    cId,
    cCode,
    inAorB
)
VALUES
(1, 'ABC', NULL),
(2, 'JKL', NULL),
(3, 'PQR', NULL);

--UPDATING inAorB 
--LEFT JOIN C TABLE WITH TABLE A AND TABLE B
UPDATE C
SET C.inAorB = CASE
                   WHEN A.aId IS NOT NULL THEN
                       'A'
                   WHEN B.bId IS NOT NULL THEN
                       'B'
               END
FROM @C_Table AS C
    LEFT JOIN @A_Table AS A
        ON A.aCode = C.cCode
    LEFT JOIN @B_Table AS B
        ON B.bCode = C.cCode
WHERE YOUR CONDITIONS..

CodePudding user response：

You can do a LEFT JOIN to each of the source tables, then use a CASE to sort out which one yields a match. Since CASE evaluates in order, you can also use it to decide which table to give precedence to if you should have to deal with codes in both tables.

UPDATE c
SET inAorB = CASE 
               WHEN a.aCode IS NOT NULL THEN 'A'
               WHEN b.bCode IS NOT NULL THEN 'B'
               ELSE NULL
             END
FROM
  c
LEFT JOIN
  a
    ON a.aCode = c.cCode
LEFT JOIN
  b
    ON b.bCode = c.cCode;