samedi 12 mars 2016

Can I reference a Sqlite CTE in more than one DML statement?

In a single statement, I want to update one set of rows and delete another set, both based on some CTE's. However, when I execute it, the second DML, the DELETE, complains as if the CTE's are no longer available. Is this not possible in Sqlite?

I looked at the WITH reference for Sqlite but the only thing I found was in the Caveat section and that was about compound statements, which is not what I am doing here.

Here is my SQL with the contents of the CTE's removed because it has some proprietary stuff in there and because I don't think it matters to the question (but let me know if that's not correct):

with cteOldAboutCmds as (
  ...
)
, cteFrequency as (
  ...
)
, cteNewAboutCmdMaster as (
  ...
)

update cmds 
set freqrankalltime = freqrankalltime + (select fr from cteFrequency) 
where id = (select id from cteNewAboutCmdMaster);

delete 
from cmds
where id in (select id from cteOldAboutCmds);

I also tried wrapping the above in begin transaction; and commit transaction; but that didn't help.

The error message is: "no such table: cteOldAboutCmds".

Aucun commentaire:

Enregistrer un commentaire