1个表中的故障设置列，第2个表的情况选择

我觉得我做错了。我是SQL Server的新手，对C#也相当精通。

我有两张表，如图所示：

表1:OOPool

[PO#]        BIGINT       NOT NULL,
[PartNumber] VARCHAR (50) NOT NULL,
[DateRec]    DATE         NOT NULL,
[DateDue]    DATE         NOT NULL,
[QTY]        INT          NOT NULL,
[Priority]   SMALLINT     NULL,
[CycleValue] INT          NULL

表2:PORecord

[PO#]           BIGINT       NOT NULL,
[PartNumber]    VARCHAR (50) NOT NULL,
[DateReceived]  DATE         NULL,
[DateDue]       DATE         NULL,
[QTYOpen]       SMALLINT     NOT NULL,
[DateCompleted] DATE         NULL,
[QTYCompleted]  SMALLINT     NULL,
[WHC]           VARCHAR (50) NULL,
[Completed]     BIT          CONSTRAINT [DF_PORecord_Completed] DEFAULT ((0)) NULL,
[IsHOT]         BIT          CONSTRAINT [DF_PORecord_IsHOT] DEFAULT ((0)) NOT NULL,
[LOS]           BIT          CONSTRAINT [DF_PORecord_LOS] DEFAULT ((0)) NOT NULL,
[IsCO]          BIT          CONSTRAINT [DF_PORecord_IsCO] DEFAULT ((0)) NOT NULL,
[IsPP]          BIT          CONSTRAINT [DF_PORecord_IsPP] DEFAULT ((0)) NOT NULL,
[OutsidePick]   BIT          CONSTRAINT [DF_PORecord_OutsidePick] DEFAULT ((0)) NOT NULL,
[Machining]     BIT          CONSTRAINT [DF_PORecord_Machining] DEFAULT ((0)) NOT NULL,
[RecentUser]    VARCHAR (50) NULL,
[LastUpdate]    DATE         NULL

OOPool用于未结订单，所有NOT NULL数据在另一个INSERT INTO命令中提供。我现在尝试通过比较IsHot、IsCO和LOS列的值来提供Priority列的值，以返回单个值。

这就是我尝试的：

UPDATE OOPool
SET Priority = (SELECT CASE
    WHEN LOS = 1 THEN 0
    WHEN (IsHot = 1) and (IsCO = 1) THEN 1
    WHEN (IsCO = 1) and (IsHot = 0) THEN 2
    WHEN (IsHOT = 1) and (IsCo = 0) THEN 3
    ELSE 4
    END
FROM PORecord
WHERE PORecord.PO# = OOPool.PO#)

目前，我得到错误

子查询返回了多个值。当子查询跟随…时，这是不允许的。。。

我正在考虑返回并重组我的PORecord表，使其只有一列名为Priority以匹配OOPool，然后让应用程序在推送到DB之前提供比较结果，但这是一项艰巨的工作（我还必须转换当前数据）。

我只是做错了吗？还是我像我想的那样做错了？

编辑：事实证明我是对的。我做错了，这是因为我对JOIN缺乏了解。

我使用了Pradeep的回应，效果非常好。

使用的代码：

UPDATE A
SET A.Priority = B.col
FROM OOPool A
    JOIN (SELECT PO#,
              CASE
                  WHEN LOS = 1 THEN 0
                  WHEN (IsHot = 1) AND (IsCO = 1) THEN 1
                  WHEN (IsCO = 1) and (IsHot = 0) THEN 2
                  WHEN (IsHot = 1) and (IsCO = 0) THEN 3
                  ELSE 4
              END col
          FROM PORecord) b
ON b.PO# = A.PO#

UPDATE p
SET Priority = 
    CASE
        WHEN r.LOS = 1 THEN 0
        WHEN r.IsHot = 1 and r.IsCO = 1 THEN 1
        WHEN r.IsCO = 1 and r.IsHot = 0 THEN 2
        WHEN r.IsHOT = 1 and r.IsCo = 0 THEN 3
        ELSE 4
    END
FROM OOPool p
    JOIN PORecord r ON p.[PO#] = r.[PO#]

UPDATE A 
SET    A.priority = B.col 
FROM   oopool A 
       JOIN (SELECT po#, 
                    CASE 
                      WHEN los = 1 THEN 0 
                      WHEN ( ishot = 1 ) 
                           AND ( isco = 1 ) THEN 1 
                      WHEN ( isco = 1 ) 
                           AND ( ishot = 0 ) THEN 2 
                      WHEN ( ishot = 1 ) 
                           AND ( isco = 0 ) THEN 3 
                      ELSE 4 
                    END COL 
             FROM   porecord) B 
         ON B.po# = A.po#