Hallo

Welkom, Gast. Alsjeblieft inloggen of registreren.

Recent

221 gasten, 0 leden

Welkom, Gast. Alsjeblieft inloggen of registreren.

28 maart 2024, 16:45:05

Login met gebruikersnaam, wachtwoord en sessielengte

Nieuws

Welkom op het vernieuwde NL Computer Forum!

Auteur Topic: SQL server update query  (gelezen 20232 keer)

0 leden en 1 gast bekijken dit topic.

Offline NLCOMP

  • Forumheld
  • *****
  • Berichten: 14.666
    • NL Computer Forum
SQL server update query
« Gepost op: 9 november 2009, 19:39:44 »
Bericht 1 van 3

NL Computer Forum ~ SQL & Programmeren
 Van:John Kopmels (Sysop)Datum:20-04-2005
 Aan:Hugo KornelisMsgID:2207.1
 Onderwerp:sql server update queryForum:ws-nlcomputer
Hoi Hugo,

Ik zie waarschijnlijk iets heel simpels over het hoofd maar heb problemen met een update query

in Access kan je de tabellen joinen en direkt een update doen, maar sql server staat maar 1 tabel
toe (degene die geupdate wordt) en de join moet als het ware in een subquery plaatsvinden

als voorbeeld :

UPDATE    dbo.WOZTABEL53

SET           RELEVREDENAFW =
                          (SELECT        RR
                            FROM          dbo.tblObject
                            WHERE      (dbo.WOZTABEL53.VNUMMARKTGEG = dbo.tblObject.VMI) AND
                                                 (dbo.WOZTABEL53.WNUM = dbo.tblObject.Woznr) AND
                                                 (RR IS NOT NULL)

WHERE      (IID = @IID) AND (MUTEINDE = 0)

wat ik hier wil bereiken is dat WOZTABEL53.RELEVREDENAFW wordt geupdate maar alleen
dan als de waarde in tblObject.RR niet Null is, anders de bestaande waarde laten staan

de update werkt ook, alleen wordt de waarde WOZTABEL53.RELEVREDENAFW ook leeg
geggooid als er niets staat in tblObject.RR en dat is niet de bedoeling, dan moet de eventueel
aanwezige bestaande waarde blijven staan, in Access is dit een eitje maar in sql server niet ?

de subquery geeft ook alleen de gevulde waardes in RR dus dat is het niet,
ik moet dus nog een voorwaarde meegeven maar zie even niet hoe en wat ....

Groetjes --John









Bericht 2 van 3

NL Computer Forum ~ SQL & Programmeren
 Van:Hugo KornelisDatum:21-04-2005
 Aan:John Kopmels (Sysop)MsgID:2207.2
 Onderwerp:sql server update queryForum:ws-nlcomputer
Hoi John,
in Access kan je de tabellen joinen en direkt een update doen, maar sql server staat maar 1 tabel
toe (degene die geupdate wordt) en de join moet als het ware in een subquery plaatsvinden

Dit is niet geheel correct. SQL Server kent ook een update op een gejoinde tabel. (De UPDATE... FROM syntax). Deze heeft echter een aantal nadelen. Om te beginnen is deze syntax geen deel van de ANSI standaard; je code is dus niet portable als je 'm gebruikt. Veel databases ondersteunen deze syntax niet; de producten die dat wel doen hebben vaak weer onderlingen verschillen - zelfs tussen producten van dezelfde fabrikant (Access en SQL Server) die beide een dergelijke syntax kennen, zitten onderlinge verschillen. Verder zijn er een aantal specifieke gevallen waarin deze syntax niet wordt toegestaan (bv. bij het gebruik van views met een INSTEAD OF UPDATE trigger). Maar het belangrijkste probleem met de SQL Server UPDATE ... FROM syntax is wel dat deze tot volkomen onvoorspelbare resultaten kan leiden als je per ongeluk de join criteria niet geheel correct opgeeft, waardoor een rij uit de te updaten tabel wordt gekoppeld aan meer dan één rij uit de andere tabel(len) - de ANSI standaard syntax geeft in zo'n geval een duidelijke foutmelding, zodat je weet dat je nog werk te doen hebt; de UPDATE ... FROM syntax kiest een willekeurig resultaat en als dat tijdens het testen goed lijkt loop je het risico het pas maanden later te ontdekken, als een flink aantal gegevens in je produktie database al vernaggeld is.
Maar het kan dus wel. En ik gebruik hem ook wel eens (met name als ik meerdere rijen moet updaten op basis van gegevens uit dezelfde subquery - de ANSI syntax vereist dan meermalen herhalen van dezelfde subquery, en omdat de optimizer dit niet herkent is het resultaat niet alleen een hele lange opdracht, maar ook een hele langzame...)
als voorbeeld :

UPDATE    dbo.WOZTABEL53

SET           RELEVREDENAFW =
                          (SELECT        RR
                            FROM          dbo.tblObject
                            WHERE      (dbo.WOZTABEL53.VNUMMARKTGEG = dbo.tblObject.VMI) AND
                                                 (dbo.WOZTABEL53.WNUM = dbo.tblObject.Woznr) AND
                                                 (RR IS NOT NULL)

WHERE      (IID = @IID) AND (MUTEINDE = 0)
Hmmm, ik neem aan dat er na (RR IS NOT NULL) nog een extra sluithaak moet staan?
wat ik hier wil bereiken is dat WOZTABEL53.RELEVREDENAFW wordt geupdate maar alleen
dan als de waarde in tblObject.RR niet Null is, anders de bestaande waarde laten staan

de update werkt ook, alleen wordt de waarde WOZTABEL53.RELEVREDENAFW ook leeg
geggooid als er niets staat in tblObject.RR en dat is niet de bedoeling, dan moet de eventueel
aanwezige bestaande waarde blijven staan

Klopt. Elke rij die niet wordt uitgesloten in de WHERE clause zal worden ge-update. Het resultaat van de gecorreleerde subquery is dan óf een waarde (geen probleem, die waarde wordt toegekend zoals jij wilt), óf ,als RR NULL is, een lege verzameling. En voor UPDATE ... SET kolom = {lege set} wordt de lege set keurig naar NULL geconverteerd en dan aan de kolom toegekend.
Een oplossing die je kunt gebruiken is het uitbreiden van de WHERE met een voorwaarde die ervooor zorgt dat rijen waarvoor de subquery geen resultaat zou opleveren worden uitgesloten van de UPDATE - dit vereist het herhalen van de subquery in de WHERE clause. Een alternatief is het gebruik van COALESCE (equivalent met IIf in Access) om een NULL uit de subquery te vervangen door de originale waarde - in dat geval wordt de rij wél ge-update, maar is de nieuwe waarde gelijk aan de oude waarde. Zeker als je triggers op de tabel hebt gedefinieerd moet je uitkijken met deze variant, omdat je meer rijen bijwerkt dan nodig (en die rijen dus ook weer door de trigger verwerkt worden).
SQL voor oplossing 1:UPDATE dbo.WOZTABEL53
SET    RELEVREDENAFW = (SELECT RR
                        FROM   dbo.tblObject
                        WHERE  dbo.WOZTABEL53.VNUMMARKTGEG = dbo.tblObject.VMI
                        AND    dbo.WOZTABEL53.WNUM = dbo.tblObject.Woznr
                        AND    RR IS NOT NULL)                 -- Deze regel kan eventueel weg
WHERE  IID = @IID
AND    MUTEINDE = 0
AND    EXISTS          (SELECT *
                        FROM   dbo.tblObject
                        WHERE  dbo.WOZTABEL53.VNUMMARKTGEG = dbo.tblObject.VMI
                        AND    dbo.WOZTABEL53.WNUM = dbo.tblObject.Woznr
                        AND    RR IS NOT NULL)
SQL voor oplossing 2:UPDATE dbo.WOZTABEL53
SET    RELEVREDENAFW = COALESCE (
                       (SELECT RR
                        FROM   dbo.tblObject
                        WHERE  dbo.WOZTABEL53.VNUMMARKTGEG = dbo.tblObject.VMI
                        AND    dbo.WOZTABEL53.WNUM = dbo.tblObject.Woznr
                        AND    RR IS NOT NULL)                 -- Deze regel kan eventueel weg
                      , RELEVREDENAFW)
WHERE  IID = @IID
AND    MUTEINDE = 0
NB: Beide update statements heb ik niet getest!
Groetjes, Hugo



Bericht 3 van 3

NL Computer Forum ~ SQL & Programmeren
 Van:John Kopmels (Sysop)Datum:21-04-2005
 Aan:Hugo KornelisMsgID:2207.3
 Onderwerp:sql server update queryForum:ws-nlcomputer
Hoi Hugo,
in Access kan je de tabellen joinen en direkt een update doen, maar sql server staat maar 1 tabel
toe (degene die geupdate wordt) en de join moet als het ware in een subquery plaatsvinden

> Dit is niet geheel correct. SQL Server kent ook een update op een gejoinde tabel.
> (De UPDATE... FROM syntax). Deze heeft echter een aantal nadelen.
> Om te beginnen is deze syntax geen deel van de ANSI standaard <knip>

dit wist ik nog niet, ik zal er naar kijken maar ga hier verder met de volgende oplossingen

als voorbeeld :

UPDATE    dbo.WOZTABEL53
SET           RELEVREDENAFW =
                          (SELECT        RR
                            FROM          dbo.tblObject
                            WHERE      (dbo.WOZTABEL53.VNUMMARKTGEG = dbo.tblObject.VMI) AND
                                                 (dbo.WOZTABEL53.WNUM = dbo.tblObject.Woznr) AND
                                                 (RR IS NOT NULL)
WHERE      (IID = @IID) AND (MUTEINDE = 0)

> ik neem aan dat er na (RR IS NOT NULL) nog een extra sluithaak moet staan?

ja en die staat er nog steeds , alleen niet mee gekopieerd :-)

wat ik hier wil bereiken is dat WOZTABEL53.RELEVREDENAFW wordt geupdate maar alleen
dan als de waarde in tblObject.RR niet Null is, anders de bestaande waarde laten staan
de update werkt ook, alleen wordt de waarde WOZTABEL53.RELEVREDENAFW ook leeg
geggooid als er niets staat in tblObject.RR en dat is niet de bedoeling, dan moet de eventueel
aanwezige bestaande waarde blijven staan

> Klopt. Elke rij die niet wordt uitgesloten in de WHERE clause zal worden ge-update.
> Het resultaat van de gecorreleerde subquery is dan óf een waarde (geen probleem,
> die waarde wordt toegekend zoals jij wilt), óf ,als RR NULL is, een lege verzameling.
> En voor UPDATE ... SET kolom = {lege set} wordt de lege set keurig naar NULL ge-
> converteerd en dan aan de kolom toegekend.

ok ik zie nu waar het fout ging
> Een oplossing die je kunt gebruiken is het uitbreiden van de WHERE
> met een voorwaarde die ervooor zorgt dat rijen waarvoor de subquery
> geen resultaat zou opleveren worden uitgesloten van de UPDATE -
> dit vereist het herhalen van de subquery in de WHERE clause. 

UPDATE dbo.WOZTABEL53
SET    RELEVREDENAFW = (SELECT RR
                        FROM   dbo.tblObject
                        WHERE  dbo.WOZTABEL53.VNUMMARKTGEG = dbo.tblObject.VMI
                        AND    dbo.WOZTABEL53.WNUM = dbo.tblObject.Woznr)
WHERE  IID = @IID
AND    MUTEINDE = 0
AND    EXISTS          (SELECT *
                        FROM   dbo.tblObject
                        WHERE  dbo.WOZTABEL53.VNUMMARKTGEG = dbo.tblObject.VMI
                        AND    dbo.WOZTABEL53.WNUM = dbo.tblObject.Woznr
                        AND    RR IS NOT NULL)


dit werkt goed

[/]
> alternatief is het gebruik van COALESCE  om een NULL uit de subquery te vervangen door de originale
> waarde , in dat geval wordt de rij wél ge-update, maar is de nieuwe waarde gelijk aan de oude waarde.
> Zeker als je triggers op de tabel hebt gedefinieerd moet je uitkijken met deze variant, omdat je meer rijen
> bijwerkt dan nodig (en die rijen dus ook weer door de trigger verwerkt worden).

UPDATE dbo.WOZTABEL53
SET    RELEVREDENAFW = COALESCE (
                       (SELECT RR
                        FROM   dbo.tblObject
                        WHERE  dbo.WOZTABEL53.VNUMMARKTGEG = dbo.tblObject.VMI
                        AND    dbo.WOZTABEL53.WNUM = dbo.tblObject.Woznr)                
                      , RELEVREDENAFW)
WHERE  IID = @IID
AND    MUTEINDE = 0


deze werkt ook goed + is aanmerkelijk sneller dan vorige

het betreft hier (in onderhavig geval) altijd een relatief kleine dataset en geen triggers

wederom heel erg bedankt voor de hulp en de bijbehorende uitleg !

ik kan weer vooruit (zat hier echt even mee vast)

Groetjes John