Updates and deletes with joins

Started by Gordonover 17 years ago2 messagesgeneral
Jump to latest
#1Gordon
gordon.mcvey@ntlworld.com

I'm working on a CMS, one of the features of the CMS in question is
that only one user can edit an item at any given time. I've
implemented this by having one table that holds the items, and another
table that holds locks. A lock row consists of the ID of the item
locked, a timestamp indicating when the item was locked, an interval
indicating when the locks expires and a string that holds a reason for
the item currently being locked.

I want to be able to restrict any query that updates or deletes from
the articles table so that they can only occur if there isn't a
corresponding entry in the locks table. As far as I can tell,
however, you can't join tables when doing updates or deletes. I know
on the PHP side I can attempt to do a select on the locks table and
only perform the delete if the select returns 0 rows, but I'd rather
the update or delete query itself does the checking. Can anyone help
out?

#2Alan Hodgson
ahodgson@simkin.ca
In reply to: Gordon (#1)
Re: Updates and deletes with joins

On Tuesday 19 August 2008, Gordon <gordon.mcvey@ntlworld.com> wrote:

I want to be able to restrict any query that updates or deletes from
the articles table so that they can only occur if there isn't a
corresponding entry in the locks table. As far as I can tell,
however, you can't join tables when doing updates or deletes. I know
on the PHP side I can attempt to do a select on the locks table and
only perform the delete if the select returns 0 rows, but I'd rather
the update or delete query itself does the checking. Can anyone help
out?

The best way to implement this is through triggers on the target tables.

However, you can also do joins with updates and deletes (UPDATE ... FROM and
DELETE ... USING).

--
Alan