¿Funciona con la declaración con múltiples comandos sql? Tengo que eliminar entradas en 2 tablas. Mi sql es así:
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))Esto no funciona. De 2 declaraciones de eliminación si comenta una, entonces la otra funciona. Pero juntos ni siquiera compilan. Definitivamente puedo escribir 2 scripts sql cada uno con 1 eliminación, pero quiero hacer esto de una sola vez. me sale este 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: 410Puedes hacer lo siguiente:
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));