Het Verborgen SQL Server Juweeltje: UPDATE from SELECT
3 Eenvoudige Manieren om UPDATE from SELECT te gebruiken maken Uw Werk gemakkelijker
U bent waarschijnlijk al eens in deze situatie geweest: U moest de gegevens in een tabel bijwerken met behulp van informatie die in een andere tabel was opgeslagen. Ik ontmoet vaak mensen die nog niet gehoord hebben van de krachtige UPDATE from SELECT oplossing die SQL Server biedt voor dit probleem. Ik heb zelf ook een hele tijd met dit probleem geworsteld voordat ik dit juweeltje ontdekte.
In de volgende regels ga ik jullie drie trucs laten zien die mijn leven al vele malen eenvoudiger hebben gemaakt. Daarvoor hebben we eerst twee tabellen nodig:
Als u wilt meelopen, kunt u het script hier ophalen: The Hidden SQL Server Gem – UPDATE from SELECT.sql
Ontwikkel uw UPDATE from a SELECT
Nu we de omgeving hebben opgezet, laten we eens duiken in hoe we dit kunnen laten werken. Voordat ik je de multi-table oplossing laat zien, demonstreer ik de eenvoudigste vorm van de UPDATE FROM syntaxis en laat ik je een eenvoudige truc zien om het ontwikkelen van je UPDATE statements heel eenvoudig te maken, door eerst een SELECT statement te schrijven en het dan te veranderen in een update door twee karakters te verwijderen. Geïntrigeerd?
De tabel dbo.Orders bevat een kolom is_archived. Deze wordt gebruikt om orders ouder dan 90 dagen te archiveren. We hebben echter de extra regel dat orders die nog niet zijn betaald of verzonden niet kunnen worden gearchiveerd. (In dit voorbeeld heeft de kolom is_archived geen fysiek effect. Zie mijn SQL Server Pro artikel Using Table Partitions to Archive Old Data in OLTP Environments als u geïnteresseerd bent in hoe u de is_archived kolom in een daadwerkelijke archiefschakelaar kunt veranderen.)
Een SELECT statement om de archiveerbare records te retourneren zou er als volgt uitzien:
FROM dbo.Orders AS O
WAAR O.order_date < DATEADD(DAY,-90,SYSDATETIME())
AND O.is_paid = 1
AND O.is_shipped = 1;
Nu gaan we er een beetje magie aan toevoegen:
SELECT *, — */
is_archived = 1
FROM dbo.Orders AS O
WHERE O.order_date < DATEADD(DAY,-90,SYSDATETIME())
AND O.is_paid = 1
AND O.is_shipped = 1;
Dit is nog steeds een eenvoudig SELECT statement. We hebben alleen twee opmerkingen en een extra kolom toegevoegd. Die extra kolom maakt gebruik van de ongebruikelijke alias syntaxis name = value, die equivalent is aan de meer gebruikelijke value AS name syntaxis.
Het mooie hiervan is dat ik van deze select een syntactisch correct UPDATE statement kan maken, gewoon door de twee streepjes voor het UPDATE keyword te verwijderen:
SELECT *, — */
is_archived = 1
FROM dbo.Orders AS O
WHERE O.order_date < DATEADD(DAY,-90,SYSDATETIME())
AND O.is_paid = 1
AND O.is_shipped = 1;
Dit werkt de dbo.Orders tabel bij net zoals een UPDATE dbo.Orders SET… statement dat zou doen, omdat de dbo.Orders tabel is ge-aliased als O en de UPDATE refereert aan diezelfde O alias.
Een JOIN toevoegen aan het UPDATE statement
De vraag die ons hier liet was hoe we gegevens in een tabel kunnen gebruiken om een andere tabel bij te werken. Zou het niet mooi zijn als we gewoon konden “JOINEN”? Goed nieuws: De bovenstaande syntaxis stelt ons in staat om precies dat te doen.
Recentelijk werd een order_count kolom toegevoegd aan de dbo.Customers tabel. Onze taak is nu om die kolom de juiste waarde te geven op basis van de werkelijke orders die elke klant heeft geplaatst. We beginnen weer door eerst een select te schrijven:
SELECT *, — */
order_count = OA.cnt
FROM dbo.Customers AS C
JOIN(
SELECT O.customer_id,
COUNT(1) cnt
FROM dbo.Orders AS O
GROUP BY O.customer_id
)OA
ON C.customer_id = OA.customer_id;
Als het SELECT statement eenmaal de juiste resultaten oplevert, is het eenvoudig om te schakelen naar UPDATE:
SELECT *, — */
order_count = OA.cnt
FROM dbo.Customers AS C
JOIN(
SELECT O.customer_id,
COUNT(1) cnt
FROM dbo.Orders AS O
GROUP BY O.customer_id
)OA
ON C.customer_id = OA.customer_id;
Door het gebruik van de alias C, weet SQL Server dat hij de dbo.Customers tabel te updaten, terwijl de benodigde informatie uit andere tabellen waarnaar in het statement wordt verwezen, wordt opgehaald.
UPDATE gebruiken met een CTE
Als CTE’s je ding zijn, kun je zelfs nog een stap verder gaan met dit. Zolang SQL Server gemakkelijk kan bepalen wat u van plan bent te updaten, kunt u in feite een CTE direct “UPDATE” met een zeer vergelijkbare syntaxis:
(
SELECT O.customer_id,
COUNT(1) cnt
FROM dbo.Orders AS O
GROUP BY O.customer_id
),
customer_order_counts AS
(
SELECT
C.customer_id,
C.name,
C.order_count,
OC.cnt new_order_cnt
FROM dbo.Customers AS C
JOIN order_counts AS OC
ON C.customer_id = OC.customer_id
)
UPDATE COC SET /*
SELECT *, — */
order_count = COC.new_order_cnt
FROM customer_order_counts AS COC;
Het enige wat u niet kunt doen met een van de bovenstaande statements, is gegevens in twee tabellen tegelijk bijwerken.
Een woord van voorzichtigheid
Deze syntax biedt ons een zeer krachtige manier om UPDATE verklaringen te schrijven die gegevens uit meer dan één tabel nodig hebben. Wees echter voorzichtig dat je geen code schrijft die willekeurig gedrag vertoont. Kijk eens naar dit UPDATE statement:
Net zoals de gemarkeerde SELECT een klant met meerdere bestellingen meerdere keren retourneert, zou de UPDATE elke klant gelukkig meerdere keren bijwerken, waarbij elke keer de vorige wijziging wordt overschreven. Alleen de laatste wijziging zou blijven bestaan.
Het probleem daarmee is dat er geen garantie is in welke volgorde dat gebeurt. De volgorde is afhankelijk van het gekozen uitvoeringsplan en kan elk moment veranderen. Dus terwijl het bovenstaande statement er misschien toe leidt dat de laatste orderdatum in je tests wordt geschreven, zal het waarschijnlijk in een andere volgorde worden uitgevoerd zodra het statement een grote hoeveelheid gegevens tegenkomt. Dit zal leiden tot moeilijk te debuggen problemen, dus let op deze mogelijkheid, wanneer je je UPDATE FROM SELECT statements schrijft.