比较两个表之间的数据并填充标志列

Question

我有tablea和tableb

Table A:
Create table TableA (ID INT, A1 DECIMAL(6,5), A2 DECIMAL(6,5), A3 DECIMAL(6,5), A4 DECIMAL(10,9), FLAGB1 CHAR(1), FLAGB2 CHAR(1))
INSERT INTO TABLEA (ID, A1, A2, A3, A4)
VALUES(1, 1.1111, 2.2222, 3.3333, 4.4444 ), (2, 1.2345, 2.2345, 3.2345, 4.2345),(3, 1.2222, 2.2222, 3.2222, 4.2222)
TABLE B:
CREATE TABLE TABLEB (ID INT, B1 DECIMAL(6,5), B2 DECIMAL(6,5))
INSERT INTO TABLEB (ID, B1, B2)
VALUES (1, 1.1111, 1.2345), (2, 2.2222, 3.3333), (3, 5.2222,3.2222)

我想将B1与A1, A2, A3, A4进行比较，如果有任何匹配号码，则将'y'放在 FLAGB1上，否则将'n'。

同样，将B2与A1, A2, A3, A4进行比较，如果有任何匹配号码，则将'y'放在 FLAGB2上，否则将'n'放在TABLEA上。

注意：实际表有数千行和大约60列。

OUTPUT:
TABLEA:
ID----A1-----------A2-----------A3----------A4-----FLAGB1----FLAGB2
1---1.11110-----2.22220------3.33330------4.44440-----Y---------N
2---1.23450-----2.23450------3.23450------4.23450-----N---------N
3---1.22220-----2.22220------3.22220------4.22220-----N---------Y

到目前为止，我已经尝试过：

    DECLARE @NUM1 decimal(6,5), @NUM2 decimal(6,5)
    DECLARE @END INT, @COUNT INT
    SET @COUNT= 1
    SELECT @END =MAX(ID) FROM TABLEB
    WHILE @COUNT <= @END
    BEGIN
        SELECT @NUM1= B1  FROM TABLEB WHERE ID = @COUNT         
        IF EXISTS (SELECT 1 FROM TABLEA WHERE @NUM1 IN (A1,A2,A3,A4) AND ID =@count)
            UPDATE TABLEA               
              SET FLAGB1 = 'Y'
            where ID = @count
                            ELSE
                                 UPDATE TABLEA
                                 SET FLAGB1='N'
                                 WHERE ID=@COUNT
             SELECT @NUM2= B2  FROM TABLEB WHERE ID = @COUNT            
      IF EXISTS (SELECT 1 FROM TABLEA WHERE @NUM2 IN (A1,A2,A3,A4) AND ID =@count)
            UPDATE TABLEA               
                SET FLAGB2 = 'Y'
            where ID = @count
                            ELSE
                                 UPDATE TABLEA
                             SET FLAGB2='N'
                                 WHERE ID=@COUNT
             SET @COUNT-@COUNT+1
    END
                   SELECT * FROM TABLEA

我在不满足逻辑的标志列上得到随机的'y'和'n'。任何帮助都将不胜感激！

Answer 1

只要您不引入循环，我似乎很简单。

UPDATE a
SET FLAGB1 = CASE WHEN EXISTS 
  (SELECT 1 FROM TABLEB WHERE b.B1 in (a.A1, a.A2, a.A3, a.A4)) 
   THEN 'Y' ELSE 'N'  END,
    FLAGB2 = CASE WHEN EXISTS 
   (SELECT 1 FROM TABLEB WHERE b.B2 in (a.A1, a.A2, a.A3, a.A4)) 
   THEN 'Y' ELSE 'N'  END
FROM TABLEA a