Tricky condition for PostgreSQL UPDATE query I need help with a PostgreSQL query.
I have a Django app + pg backend, where users post interesting URLs (or links) and can then comment under such links (called replies). There are two pg tables to represent this: links_link and links_publicreply.
I am to delete aged rows from links_publicreply. That's easy enough:
DELETE FROM links_publicreply WHERE &quot;submitted_on&quot; &lt; now() - interval '7 days';

But some of these are referenced in the links_link table, hence the query fails. The reference in the links_link table is:
latest_reply = models.ForeignKey
    (
        'links.Publicreply', 
        blank=True, 
        null=True, 
        on_delete=models.SET_NULL
    )

My first question is: How do I delete these foreign key references too?
My hunch is:
begin;
UPDATE links_link SET latest_reply=NULL where latest_reply_id in 
    (SELECT id FROM links_publicreply where &quot;submitted_on&quot; &lt; now() - interval '7 days');
DELETE FROM links_publicreply WHERE &quot;submitted_on&quot; &lt; now() - interval '7 days';
commit;

Does this look right?
If you read the update query above closely, you'll notice it sets latest_reply to null wherever it pointed to an aged links_publicreply row.
I don't actually want to do that. I ideally want to set latest_reply to the next available links_publicreply record (associated to the particular links_link record), which didn't get deleted by the query:
DELETE FROM links_publicreply WHERE &quot;submitted_on&quot; &lt; now() - interval '7 days'; 

How do I do that? That's my second question.

Tables
PG tables, as described in my Django app's models.py, are as follows:
class Link(models.Model):
    description = models.TextField(validators=[MaxLengthValidator(500)])
    submitter = models.ForeignKey(User)
    submitted_on = models.DateTimeField(auto_now_add=True)
    reply_count = models.IntegerField(default=0)
    latest_reply = models.ForeignKey
        ('links.Publicreply', blank=True, null=True, on_delete=models.SET_NULL)

class Publicreply(models.Model):
    submitted_by = models.ForeignKey(User)
    answer_to = models.ForeignKey(Link)
    submitted_on = models.DateTimeField(auto_now_add=True)
    description = models.TextField(validators=[MaxLengthValidator(250)])
Just add an ON DELETE CASCADE option to your foreign key:

ALTER TABLE links_link 
DROP CONSTRAINT constraint_name,   
ADD CONSTRAINT constraint_name FOREIGN KEY (latest_reply_id)
      REFERENCES links_publicreply(id) ON DELETE CASCADE;


Apparently in django the above is translated as:

on_delete=models.CASCADE