Блог

У меня есть устаревшая таблица с составным ключом из 2 полей и стандартным символьным полем: Order_Id, Item_Id, Type . Проблема, с которой мы столкнулись, заключается в том, что некоторые типы являются пустыми, и мы хотим их исправить.

Я пытаюсь написать (если возможно) инструкцию UPDATE, которая обновила бы любые пустые записи с типом непустой записи с тем же Order_Id типом. Одним из ограничений является то, что ничего не должно произойти, если есть две записи с одинаковыми Order_Id , но разными типами.

 Order_ID   Item_ID    Type
1          1          'A'
1          2          ''   <-- Update to A, matches Rec 1, 1
2          1          'A'
2          2          'B'
2          3          ''   <-- Can't update because items 1 and 2 differ
  

Можно ли разумно выполнить этот тип операции? Я дошел до создания самосоединяющегося результирующего набора (из которого я могу обновлять), но я не понял, как работать с разными элементами.

 SELECT
    a.Order_Id,
    a.Item_Id,
    a.[Type],
    b.[Type]
FROM Order_Item as a
CROSS APPLY 
(SELECT DISTINCT b.[Type] FROM Order_Item as b WHERE b.Order_Id = a.Order_ID AND b.[Type] != '') as b
  

Предполагая, что под «обновлением» вы действительно имеете в виду UPDATE , тогда вы действительно могли бы достичь этого с помощью одного CTE и пары оконных функций:

 CREATE TABLE TestTable (Order_ID int, Item_ID int, [Type] char(1));

INSERT INTO TestTable (Order_ID, Item_ID, [Type])
VALUES(1,1,'A'),
      (1,2,'' ),
      (2,1,'A'),
      (2,2,'B'),
      (2,3,'' );

GO
WITH CTE AS(
    SELECT Order_ID, Item_ID, [Type],
           MIN(NULLIF([Type],'')) OVER (PARTITION BY Order_ID) AS MinType,
           MAX(NULLIF([Type],'')) OVER (PARTITION BY Order_ID) AS MaxType
    FROM TestTable)
UPDATE CTE
SET [Type] = MinType
WHERE [Type] = ''
  AND MinType = MaxType;

SELECT *
FROM TestTable;

GO
DROP TABLE TestTable;
  

Эта пара CTE должна предоставить вам значения Order_ID, которым присвоен только один тип:

 ;With cte_Distinct_Type
As
(
    Select Distinct 
        oi.Order_ID
        , oi.Item_Type
    From dbo.Order_Item As oi
    Where oi.Item_Type <> ''
), cte_Singleton_Types
As
(
    Select
        cte.Order_ID
        , Count(cte.Order_ID) As Order_Count
    From cte_Distinct_Type As cte
    Group By 
        cte.Order_ID
    Having Count(cte.Order_ID) = 1
)
  

Остальное я оставляю в ваших умелых руках 😉

Очень похожий подход, но мне нравятся оконные функции.

 DECLARE @mytable TABLE (
ORDER_ID INT,
Item_ID INT,
Type NCHAR(1)
)

INSERT INTO @mytable
(ORDER_ID, Item_ID, Type)
VALUES
(1,1,'A'), (1,2,''),(1,3,'A'),(2,1,'A'),(2,2,'B'),(2,3,'')


;WITH CTE AS (
    SELECT X.*, COUNT(*) OVER(PARTITION BY ORDER_ID) AS num_types
    FROM
    (
        SELECT DISTINCT ORDER_ID, Type
        FROM @mytable
        WHERE Type != ''
    ) AS X
)

UPDATE a
SET a.Type = CTE.Type
FROM @mytable a
INNER JOIN CTE
ON CTE.ORDER_ID = a.ORDER_ID
WHERE CTE.num_types = 1

SELECT * FROM @mytable