Bericht 1 van 3NL Computer Forum ~ SQL & Programmeren Van | : | John Kopmels (Sysop) | Datum | : | 20-04-2005 |
Aan | : | Hugo Kornelis | MsgID | : | 2207.1 |
Onderwerp | : | sql server update query | Forum | : | 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 3NL Computer Forum ~ SQL & Programmeren Van | : | Hugo Kornelis | Datum | : | 21-04-2005 |
Aan | : | John Kopmels (Sysop) | MsgID | : | 2207.2 |
Onderwerp | : | sql server update query | Forum | : | 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 plaatsvindenDit 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 staanKlopt. 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 3NL Computer Forum ~ SQL & Programmeren Van | : | John Kopmels (Sysop) | Datum | : | 21-04-2005 |
Aan | : | Hugo Kornelis | MsgID | : | 2207.3 |
Onderwerp | : | sql server update query | Forum | : | 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