[SQL] Update met subqueries

[NMe]

Ik sla voor mijn forumapplicatie het totaal aantal berichten dat iemand geplaatst heeft ook op in de gebruikerstabel (in een Access2003 database), dit in verband met een betere performance t.o.v. het gebruiken van een COUNT functie. Nou is dat best te onderhouden bij het plaatsen van een bericht, of het verwijderen van één bericht. Het wordt een beetje problematisch wanneer je een goede update query wil bedenken als er een heel topic of zelfs meerdere topics worden verwijderd.

Nou heb ik de volgende query bedacht, die de postcount van iedere gebruiker weer goed moet zetten:

SQL:

UPDATE gebruiker g 
SET aantal_posts = (
    SELECT COUNT(*)
    FROM bericht
    WHERE gebruiker_id = g.id
)
WHERE (
    (
        (
            (
                SELECT COUNT(*)
                FROM bericht
                WHERE gebruiker_id = g.id
            )
        ) <> [g].[aantal_posts]
    )
)

(Haakjes zijn niet mijn werk, maar van Access, misschien beetje overdreven... )

Nou de bovenstaande query werkt niet, en ik heb geen idee hoe ik het anders efficiënt aan kan pakken. Ik kan het wel met een query in een loopje oplossen in ASP maar ik hoopte eigenlijk dat het met alleen SQL ook wel ging. Heeft iemand wat pointers voor me?

[whoami]

Tja, het werkt natuurlijk niet.
Je hebt WHERE en dan direct je subquery. Waarmee moet het resultaat van die subquery vergeleken worden?

Trouwens, de mooiste oplossing zou eigernlijk zijn als je een DBMS gebruikt dat triggers ondersteunt, en het updaten van dat tellertje in de trigger doet.

[SuperRembo]

Werkt dit in Access?

SQL:

UPDATE gebruiker
SET aantal_posts = count(b.id)
FROM gebruiker g
    LEFT OUTER JOIN bericht b ON b.gebruiker_id = g.id
HAVING count(b.id)<>g.aantal_posts

[NMe]

whoami schreef op 07 mei 2004 @ 11:01:
Tja, het werkt natuurlijk niet.
Je hebt WHERE en dan direct je subquery. Waarmee moet het resultaat van die subquery vergeleken worden?

De waarde die die subquery oplevert zou toch gecheckt worden op ongelijkheid met [g].[aantal_posts]? Of denk ik dan te makkelijk?

whoami schreef op 07 mei 2004 @ 11:01:
Trouwens, de mooiste oplossing zou eigernlijk zijn als je een DBMS gebruikt dat triggers ondersteunt, en het updaten van dat tellertje in de trigger doet.

Kan Access dat? Zo nee, dan kan ik helaas niet overstappen, moet hier per se Access voor gebruiken...

[P_de_B]

Ik weet niet of Access dit slikt, maar dit zou dacht ik moeten werken:

code:

UPDATE Tabel
SET AantalPosts = 
     (SELECT COUNT(*) FROM bericht WHERE gebruiker_id = g.gebruiker_id)
FROM Gebruikerstabel g
WHERE AantalPosts  <>  
    (SELECT COUNT(*) FROM bericht WHERE gebruiker_id = g.gebruiker_id)

uit m'n hoofd, dus controleer dit wel even goed

[NMe]

SuperRembo schreef op 07 mei 2004 @ 11:04:
Werkt dit in Access?

SQL:

UPDATE gebruiker
SET aantal_posts = count(b.id)
FROM gebruiker g
    LEFT OUTER JOIN bericht b ON b.gebruiker_id = g.id
HAVING count(b.id)<>g.aantal_posts

Dat pikt Access niet zo, ik heb hem herschreven naar dit:

SQL:

UPDATE gebruiker SET gebruiker.aantal_posts = (SELECT count(b.id)
FROM gebruiker g
    LEFT OUTER JOIN bericht b ON b.gebruiker_id = g.id
HAVING count(b.id)<>g.aantal_posts)

Maar dat geeft dezelfde foutmelding als de query in mijn startpost, namelijk: "De bewerking moet worden uitgevoerd op een query die kan worden bijgewerkt."

[whoami]

NMe84 schreef op 07 mei 2004 @ 11:04:

De waarde die die subquery oplevert zou toch gecheckt worden op ongelijkheid met [g].[aantal_posts]? Of denk ik dan te makkelijk?

Hoe dan? Dat staat niet in je where.

[curry684]

Ahum, ik weet mij nog vaag Topics met 311 replies te herinneren, en dat die anderhalf uur destijds een positieve schatting was (duurde uiteindelijk 2-2.5 uur geloof ik). Ik zou je toch *echt* dringend willen aanraden om die counter correct bij te werken

Voor het removen van een topic kan dat gewoon door middel van een "user_id IN (select poster_id from tables where topic_id = 684)" oid.

[NMe]

whoami schreef op 07 mei 2004 @ 11:08:
Hoe dan? Dat staat niet in je where.

WHERE (
(
(
(
SELECT COUNT(*)
FROM bericht
WHERE gebruiker_id = g.id
)
) <> [g].[aantal_posts]
)
)


@curry684: Dus alle gebruikers die voorkomen in die topic van tevoren selecteren, dan dat topic wissen en daarna met aparte queries die count goed zetten in een loopje? Dat was mijn eerste gedachte en ik denk dat ik het zo dan maar moet gaan doen als het niet anders kan.

[whoami]

Hmmm, ok.

Maareh, die fout die je krijgt duidt volgens mij niet direct op een foute query denk ik. Kan je gelijk welke andere UPDATE query wel uitvoeren?

[NMe]

whoami schreef op 07 mei 2004 @ 11:23:
Hmmm, ok.

Maareh, die fout die je krijgt duidt volgens mij niet direct op een foute query denk ik. Kan je gelijk welke andere UPDATE query wel uitvoeren?

Andere update queries gaan perfect. Ik vermoedde zelf dat het iets met het gebruik van subqueries in een update query te maken had, maar aan bovenstaande reacties te zien zal het dat wel niet zijn. Vraag is dan natuurlijk waar het hem wel zit.

[Alex]

Kun je niet simpel mbv. een scripting taal eerst de subquery uit laten voeren, en dan pas de update, met de resultaten van de eerstequery daarin geïntergreerd natuurlijk.

[NMe]

Alex de Groot schreef op 07 mei 2004 @ 11:32:
Kun je niet simpel mbv. een scripting taal eerst de subquery uit laten voeren, en dan pas de update, met de resultaten van de eerstequery daarin geïntergreerd natuurlijk.

Das niet eens zo'n slecht idee. Dan heb ik ook maar 2 queries nodig in plaats van de 3 die ik in de startpost gebruik. Zometeen even testen.

[curry684]

NMe84 schreef op 07 mei 2004 @ 11:19:
[...]
@curry684: Dus alle gebruikers die voorkomen in die topic van tevoren selecteren, dan dat topic wissen en daarna met aparte queries die count goed zetten in een loopje? Dat was mijn eerste gedachte en ik denk dat ik het zo dan maar moet gaan doen als het niet anders kan.

Je kunt met 2 queries af: eerst de postcounts in 1 klap verlagen en vervolgens in 1 DELETE de posts wegknikkeren, vandaar mijn voorbeeld met de IN sectie voor je query

Op een fatsoenlijke database zet je daar vanzelfsprekend een transaction omheen zodat er geen 'undefined state' tussen de 2 queries optreedt, maja dat is de schuld van degene die vind dat dit met Access moet

[whoami]

Access ondersteund ook transacties hoor, maar die transactie moet je dan wel beheren via je client-app.

[NMe]

curry684 schreef op 07 mei 2004 @ 11:35:
[...]

Je kunt met 2 queries af: eerst de postcounts in 1 klap verlagen en vervolgens in 1 DELETE de posts wegknikkeren, vandaar mijn voorbeeld met de IN sectie voor je query

SQL:

UPDATE gebruiker
SET aantal_posts = aantal_posts - (
   SELECT COUNT(*)
   FROM bericht
   WHERE gebruiker_id = gebruiker.id
      AND onderwerp_id = 1
)
WHERE id IN (
   SELECT gebruiker_id
   FROM bericht
   WHERE onderwerp_id = 1
)

Zoiets als eerste query? Of is dat niet wat je bedoelt?

edit:
Die doet het namelijk ook niet.

[ Voor 4% gewijzigd door NMe op 07-05-2004 12:42 ]

[NMe]

*kleine schop*

Ik snap die pointer van curry niet helemaal. Ik zie niet met welke query ik in één keer van alle gebruikers in een onderwerp de postcount kan verlagen. Het is vast wel mogelijk, maar ik zie het niet, het beste wat ik kan bedenken is de query in mijn post hierboven, maar die geeft dezelfde melding als de queries die ik en anderen in dit topic al eerder postten.

[curry684]

Die subquery met de count kan niet werken omdat je in de subquery kennis van buiten nodig hebt. Dat moet met 'correlated subqueries' zo uit het blote hoofd, maar da's wel complex.... ik zal zodadelijk eens wat onderzoek plegen

[NMe]

curry684 schreef op 08 mei 2004 @ 17:26:
Die subquery met de count kan niet werken omdat je in de subquery kennis van buiten nodig hebt. Dat moet met 'correlated subqueries' zo uit het blote hoofd, maar da's wel complex.... ik zal zodadelijk eens wat onderzoek plegen

Als dat zou kunnen, heel graag. Ik zal intussen ook eens zoeken wat correlated subqueries zijn enzow, heb er eerlijk gezegd nog nooit van gehoord.

[justmental]

curry684 schreef op 08 mei 2004 @ 17:26:
Die subquery met de count kan niet werken omdat je in de subquery kennis van buiten nodig hebt. Dat moet met 'correlated subqueries' zo uit het blote hoofd, maar da's wel complex.... ik zal zodadelijk eens wat onderzoek plegen

Waarom zou dat niet kunnen?
De info komt toch van een hoger niveau

[ Voor 182% gewijzigd door justmental op 08-05-2004 22:15 ]

[NMe]

justmental schreef op 08 mei 2004 @ 22:13:
[...]

Waarom zou dat niet kunnen?
De info komt toch van een hoger niveau

Een subquery mag niets uit de hoofdquery gebruiken, alleen andersom....meen ik. Ben helaas niet zo'n subquery genie omdat ik normaal alleen met MySQL werk. Dat zal ook wel de reden zijn dat ik hier nu vast zit.

[bigbeng]

NMe84 schreef op 10 mei 2004 @ 09:46:
[...]

Een subquery mag niets uit de hoofdquery gebruiken, alleen andersom....meen ik. Ben helaas niet zo'n subquery genie omdat ik normaal alleen met MySQL werk. Dat zal ook wel de reden zijn dat ik hier nu vast zit.

Das niet waar hoor, je mag best informatie uit een hoofdquery betrekken in een subquery.

Het probleem is dat je een select gebruikt die joint op een tabel die moet worden bijgewerkt met je update. Dit levert een circulaire referentie op en Access heeft dat (toch wel slim) blijkbaar door.

[curry684]

NMe84 schreef op 08 mei 2004 @ 21:13:
[...]

Als dat zou kunnen, heel graag. Ik zal intussen ook eens zoeken wat correlated subqueries zijn enzow, heb er eerlijk gezegd nog nooit van gehoord.

"Many queries can be evaluated by executing the subquery once and substituting the resulting value or values into the WHERE clause of the outer query. In queries that include a correlated subquery (also known as a repeating subquery), the subquery depends on the outer query for its values. This means that the subquery is executed repeatedly, once for each row that might be selected by the outer query."

Ik heb helaas dit weekend geen tijd meer gehad om tijd te steken in het uitzoeken, volgens Books Online klopt je syntax wel...

[NMe]

curry684 schreef op 10 mei 2004 @ 10:35:
[...]

"Many queries can be evaluated by executing the subquery once and substituting the resulting value or values into the WHERE clause of the outer query. In queries that include a correlated subquery (also known as a repeating subquery), the subquery depends on the outer query for its values. This means that the subquery is executed repeatedly, once for each row that might be selected by the outer query."

Ik heb helaas dit weekend geen tijd meer gehad om tijd te steken in het uitzoeken, volgens Books Online klopt je syntax wel...

Hmm, volgens mij is de syntax het probleem niet, maar, zoals bigbeng als zegt, een denkfout. Ik heb net eens gekeken waar de fout zat en dat zit hem dus inderdaad in de UPDATE blaat SET x = (willekeurige subquery hier). Dus tenzij ik ergens een duidelijk voorbeeld van correlated subqueries ga vinden zal ik denk ik met een hele hoop queries moeten gaan werken, ik heb in ieder geval al de query die alle gebruikers selecteert met een foute postcount:

SQL:

SELECT id
FROM gebruiker
WHERE aantal_posts NOT IN (
   SELECT COUNT(*)
   FROM bericht
   WHERE bericht.gebruiker_id > 0
      AND gebruiker.id = bericht.gebruiker_id
)

Maarja, ik wil liever niet teveel queries gebruiken natuurlijk...

[P_de_B]

Het voorbeeld wat ik heb gegeven maakt gebruik van een correlated subquery. Misschien kun je daat wat mee?

[NMe]

P_de_B schreef op 10 mei 2004 @ 10:50:
Het voorbeeld wat ik heb gegeven maakt gebruik van een correlated subquery. Misschien kun je daat wat mee?

Nee, die heb ik geprobeerd. Het probleem zit hem in die set, die afhangt van een subquery. Access wil dat dus niet slikken.

Edit:
Ik heb nog wat gezocht maar ik zie niet hoe een correlated subquery het probleem kan oplossen, aangezien mijn fout zit in die set. De volgende query werkt namelijk ook niet:

SQL:

UPDATE gebruiker SET aantal_posts = (SELECT COUNT(*) FROM bericht)

Gewoon zonder tests dus, en toch werkt het niet. Je zou denken dat je geen waarde kan setten dmv een subquery?

[ Voor 33% gewijzigd door NMe op 10-05-2004 11:15 ]

[ACM]

Wat bedoel je trouwens met 'werkt niet' ?
Accepteert ie de query niet? (foutmelding?)
Wordt er gewoon niks aangepast? (is er wel geschikte testdata?)

[NMe]

ACM schreef op 10 mei 2004 @ 11:45:
Wat bedoel je trouwens met 'werkt niet' ?
Accepteert ie de query niet? (foutmelding?)

Foutmelding: De berwerking moet worden uitgevoerd op een query die kan worden bijgewerkt.

ACM schreef op 10 mei 2004 @ 11:45:
Wordt er gewoon niks aangepast? (is er wel geschikte testdata?)

Er wordt niks aangepast nee, maar dat is dus omdat Access de query niet uit wil voeren. De testdata is wel geschikt lijkt me, ik heb een overvloed aan onzinberichten geplaatst die allemaal aan mijn datamodel voldoen.

[NMe]

Ik heb het nu opgelost op de manier die ik eigenlijk niet wilde gebruiken, namelijk eerst dit:

SQL:

SELECT id, (
   SELECT COUNT(*)
   FROM bericht
   WHERE gebruiker_id = g.id
) AS aantal
FROM gebruiker AS g
WHERE aantal_posts <> (
   SELECT COUNT(*)
   FROM bericht
   WHERE gebruiker_id = g.id
)

Dit haalt de gebruikerids van alle gebruikers met een foute postcount, én de goeie postcounts op, en vervolgens zet ik de postcounts met de volgende ASP code goed:

ASP.NET Visual Basic:

        While Not rs.BOF And Not rs.EOF
          query = "UPDATE gebruiker SET aantal_posts = " & CInt(rs("aantal")) & " WHERE id = " & CInt(rs("id"))
          rs2.Open query, conObj, 1, 2, 1
          
          rs.MoveNext
        Wend

Nou werkt dat wel, maar ik hoop nog steeds dat iemand me op weg kan helpen met de methode die ik hierboven geprobeerd heb.