Trouble Setting column in 1 table with case select of 2nd table

I feel like I’m going about this wrong. I’m sort of(?) new to SQL Server and fairly proficient at C#.

I have two tables set as shown:

  • selectively execute task in ssis control flow
  • Query results change after making an PK Clustered
  • How to count row in SQL SERVER 2012 using sys.partitions
  • converting minutes to hour:minutes:second in sql
  • Wrong DateTime parsing when using other cultures
  • C# : Application doesn't access database in client pc
  • Table 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
    

    Table 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 is used for the open orders, and all of the NOT NULL data is supplied in another INSERT INTO command. I’m now trying to supply the Priority column value by comparing the IsHot, IsCO, and LOS column values to return a single value.

    This is what I tried:

    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#)
    

    Currently, I’m getting the error

    Subquery returned more than 1 value. This is not permitted when the subquery follows …

    I’m thinking of going back and restructuring my PORecord table to only have 1 column named Priority to match OOPool and then have the application supply the comparison result before pushing to the DB, but that’s a lot of work (I would also have to convert the current data).

    Am I just doing it wrong? Or am I going about this wrong like I thought?

    EDIT: It turns out I was right. I was doing it wrong, and it was due to my lack of knowledge of JOIN.

    I used Pradeep’s response, and it worked perfectly.

    Code used:

    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#
    

  • Get RETURN value from stored procedure in SQL
  • #1452 - Cannot add or update a child row, REFERENCES `eav_attribute`
  • Date comparison in MS SQL 2005
  • optimize sql query
  • SQL Server - Select first row that meets criteria
  • Configuring Multiple ASP.NET MVC Sites To Use a Single Database For Authentication/Membership
  • 2 Solutions collect form web for “Trouble Setting column in 1 table with case select of 2nd table”

    It should be lyk this

    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# 
    

    You can use a JOIN in your UPDATE:

    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#]
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.