Does with statement work with multiple sql commands? I have to delete entries in 2 tables. My sql is like this:
with tbd as (
SELECT (row_value ->> 'id')::INTEGER
FROM public.row_history
where record_table = 'survey_storage'
and row_value ->> 'status' = 'Never Surveyed'
except
(SELECT (row_value ->> 'id')::INTEGER
FROM public.row_history
where record_table = 'survey_storage'
and row_value ->> 'status' = 'Ready to Launch')
limit 1)
delete from row_history where (row_value ->> 'id')::INTEGER = ANY(ARRAY(select * from tbd))
delete from survey_storage where id = ANY(ARRAY(select * from tbd))
This is not working.Out of 2 delete statement if you comment out one, then the other one works. But together they do not even compile.Definitely I can write 2 sql scripts each with 1 delete - but I want to do this in one go. I get this error:
SQL Error [42601]: ERROR: syntax error at or near "delete"
Position: 410
ERROR: syntax error at or near "delete"
Position: 410
ERROR: syntax error at or near "delete"
Position: 410
You can do like below:
with tbd as (
SELECT (row_value ->> 'id')::INTEGER
FROM public.row_history
where record_table = 'survey_storage'
and row_value ->> 'status' = 'Never Surveyed'
except
(SELECT (row_value ->> 'id')::INTEGER
FROM public.row_history
where record_table = 'survey_storage'
and row_value ->> 'status' = 'Ready to Launch')
limit 1)
), cte2 as (
delete from row_history
where (row_value ->> 'id')::INTEGER
= ANY(ARRAY(select * from tbd))
)
delete from survey_storage
where id = ANY(ARRAY(select * from tbd));