CODEWITHSUNDEEP
sqlpostgresql
Subhendu Mahanta
Subhendu Mahanta

Reputation: 1031

Why I cannot bundle two sql commands with a ''with" statement in Postgresql?

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

Upvotes: 1

Views: 89

Answers (1)

Abdusoli
Abdusoli

Reputation: 659

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));

Upvotes: 4

Related Questions