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