Syntax Error: Lateral Join with Delete (PostgreSQL)

Question

PostgreSQL 11.1

AFAIK, this is correct and should run. It Fails with syntax error on Delete. What am I missing?

Thanks for any help.

ERROR: syntax error at or near "DELETE" LINE 41: DELETE FROM d

WITH _in (tservice, patient_recid, disease_recid, new_disease_recid) AS (
        VALUES ('2021-04-21'::timestamp, '23262'::integer, '34978'::integer, '33364'::integer)    
)
UPDATE dx d
SET disease_recid = n.new_disease_recid
FROM _in n,
    LATERAL ( WITH RECURSIVE readtoend AS(
                SELECT recid, newrecid
                    FROM patients p1
                    JOIN _in n ON p1.recid = n.patient_recid
                UNION
                SELECT  c.recid, c.newrecid
                    FROM patients c
                    INNER JOIN readtoend s ON s.newrecid = c.recid
            ),
            readtostart AS(
                SELECT recid, newrecid
                        FROM patients p1
                        JOIN _in n ON p1.recid = n.patient_recid
                    UNION
                    SELECT c.recid, c.newrecid
                        FROM patients c
                        INNER JOIN readtostart s ON s.recid = c.newrecid
            )
                SELECT recid FROM readtoend
                UNION
                SELECT recid FROM readtostart            
            ) j,
    LATERAL ( WITH _get_existing_target AS(                               
                    SELECT d.*
                    FROM d
                    WHERE (d.patient_recid, d.disease_recid) = (j.recid, n.new_disease_recid) AND d.tservice <= n.tservice
                ),
                 _get_conflicts AS(                                    
                    SELECT d.*
                    FROM d  
                    WHERE (d.patient_recid, d.disease_recid) = (j.recid, n.disease_recid) AND EXISTS (  SELECT 1
                                    FROM _get_existing_target x
                                    WHERE d.patient_recid = x.patient_recid AND d.tservice::date = x.tservice::date)
                )
                DELETE FROM d
                USING _get_conflicts f
                WHERE d.recid = f.recid
                RETURNING d.*
                ) del
WHERE (d.patient_recid, d.disease_recid)  = (j.recid, n.disease_recid) AND d.tservice::date <= n.tservice::date
       AND  d.recid NOT IN ( SELECT recid FROM del);    

Answer 1

You cannot use DELETE ... RETURNING in the FROM list of a query.