Как этот скрипт обновляет таблицу при использовании LEFT JOINs?

Результат в этом скрипте правильный, но я, кажется, не понимаю, почему столбец BetaStatus остается «NOK»,

Что касается «Бета», первая строка (Beta = NOK) обновит @Summary.BetaStatus до NOK. Но потом я подумал, что последние две строки в @testTable будут обновлять BetaStatus от NOK до OK. Я просто хочу быть уверенным, что это не совпадение, что «NOK» на самом деле является последней обработанной строкой и, следовательно, значением.

 declare @testTable table ( id int, Pgroup varchar(10), Pstatus varchar(3) ) insert into @testTable select 3, 'Alpha', 'OK' insert into @testTable select 3, 'Beta', 'NOK' insert into @testTable select 3, 'Gamma', 'OK' insert into @testTable select 3, 'Beta', 'OK' insert into @testTable select 3, 'Beta', 'OK' declare @Summary table ( id int, AlphaStatus varchar(3), BetaStatus varchar(3), GammaStatus varchar(3) ) insert into @Summary (id) select 3 update @Summary set AlphaStatus = ISNULL(rA.Pstatus, AlphaStatus), BetaStatus = ISNULL(rB.Pstatus, BetaStatus), GammaStatus = ISNULL(rG.Pstatus, GammaStatus) from @Summary t left join @testTable rA on rA.id = t.ID AND rA.Pgroup = 'Alpha' left join @testTable rB on rB.id = t.ID AND rB.Pgroup = 'Beta' left join @testTable rG on rG.id = t.ID AND rG.Pgroup = 'Gamma' select * from @summary 

Причина, по которой я спрашиваю, заключается в том, что для каждого id, AlphaStatus, BetaStatus, GammaStatus не следует менять обратно на «ОК», если это было ранее «NOK». Когда он будет обновлен до «NOK», он останется таким, независимо от того, что будет дальше.

Альтернативой было обновить @Summary значениями «ОК», а затем сделать еще одно обновление с «NOK». Таким образом, я знаю, что «NOK» не будет заменен. Но если это сработает, я предпочел бы использовать это.

И как второй вопрос, почему UPDATE не работает правильно, если я использую INNER JOIN?

Благодарю.

Давайте посмотрим, какой вариант возврата вместо update

 select AlphaStatus = ISNULL(rA.Pstatus, AlphaStatus), BetaStatus = ISNULL(rB.Pstatus, BetaStatus), GammaStatus = ISNULL(rG.Pstatus, GammaStatus) from @Summary t left join @testTable rA on rA.id = t.ID AND rA.Pgroup = 'Alpha' left join @testTable rB on rB.id = t.ID AND rB.Pgroup = 'Beta' left join @testTable rG on rG.id = t.ID AND rG.Pgroup = 'Gamma' 

Результат:

 AlphaStatus BetaStatus GammaStatus OK NOK OK OK OK OK OK OK OK 

Теперь вы пытаетесь сделать UPDATE

 update @Summary set AlphaStatus = ISNULL(rA.Pstatus, AlphaStatus), BetaStatus = ISNULL(rB.Pstatus, BetaStatus), GammaStatus = ISNULL(rG.Pstatus, GammaStatus) from @Summary t left join @testTable rA on rA.id = t.ID AND rA.Pgroup = 'Alpha' left join @testTable rB on rB.id = t.ID AND rB.Pgroup = 'Beta' left join @testTable rG on rG.id = t.ID AND rG.Pgroup = 'Gamma' 

После таблицы обновлений @Summary содержит:

 id AlphaStatus BetaStatus GammaStatus 3 OK NOK OK 

Я полагаю, вы хотели получить:

 id AlphaStatus BetaStatus GammaStatus 3 OK OK OK 

Но UPDATE не работает таким образом, когда они являются множественными совпадениями, результат может быть непоследовательным и частично основан на таблице упорядочения или фактическом плане выполнения.

См. Также: Давайте откажемся от UPDATE FROM!

Корректность? Бах, кого это волнует?

Наилучшим образом, большинств делают. Вот почему мы тестируем.

Если я испортил критерии соединения в запросе SELECT, так что слишком много строк из второй таблицы совпадают, я увижу это, как только я проведу, потому что тогда я получаю больше строк назад. Если я испортил критерии подзапроса в стандартном запросе UPDATE ANSI аналогичным образом, я вижу его еще раньше, потому что SQL Server вернет ошибку, если подзапрос возвращает больше одного значения. Но с проприетарным синтаксисом UPDATE FROM я могу испортить это соединение и никогда не замечаю – SQL Server будет с радостью обновлять одну и ту же строку снова и снова, если она соответствует более одной строке в объединенной таблице, и только результат последней из эти обновления прилипают. И нет способа узнать, какая строка будет, поскольку это зависит от плана выполнения запроса, который выбирается. В худшем случае сценарий будет заключаться в том, что план выполнения просто приведет к ожидаемому результату во время всех тестов на однопроцессорном сервере разработки, а затем, после развертывания на четырехстороннем двухъядерном сервере, наши драгоценные данные внезапно попадает в фанат …

Слишком просто увидеть эту несогласованность вместо таблицы использования табличной переменной и создать кластеризованные индексы:

SqlFiddleDemo

 CREATE TABLE testTable(id int, Pgroup varchar(10), Pstatus varchar(3)); CREATE CLUSTERED INDEX clx_name ON testTable(PStatus DESC); /* or */ CREATE CLUSTERED INDEX clx_name ON testTable(PStatus ASC); 

Если вы используете MERGE, например:

 ;WITH cte as (SELECT ra.id ,AlphaStatus = rA.Pstatus ,BetaStatus = rB.Pstatus ,GammaStatus = rG.Pstatus from @Summary t left join @testTable rA on rA.id = t.ID AND rA.Pgroup = 'Alpha' left join @testTable rB on rB.id = t.ID AND rB.Pgroup = 'Beta' left join @testTable rG on rG.id = t.ID AND rG.Pgroup = 'Gamma' ) MERGE @Summary AS TGT USING (SELECT * FROM cte ) AS SRC ON TGT.id = SRC.id WHEN MATCHED THEN UPDATE SET AlphaStatus = ISNULL(src.AlphaStatus, tgt.AlphaStatus), BetaStatus = ISNULL(src.BetaStatus, tgt.BetaStatus), GammaStatus = ISNULL(src.GammaStatus, tgt.GammaStatus); 

Вы получите сообщение об ошибке, которое не допускается:

Заявление MERGE пыталось ОБНОВИТЬ или УДАЛИТЬ одну и ту же строку более одного раза . Это происходит, когда целевая строка соответствует более чем одной строке источника. Оператор MERGE не может обновлять / удалять одну и ту же строку целевой таблицы несколько раз. Уточните предложение ON, чтобы гарантировать, что целевая строка соответствует не более одной исходной строке, или используйте предложение GROUP BY для группировки исходных строк.