Cannot use LIMIT on DELETE query

Our other supported database PostgreSQL does not support any LIMIT at all on DELETE: http://www.postgresql.org/docs/8.1/static/sql-delete.html

Since this is about a missing feature of one of the functions which was not designed to be used for DELETE queries and only applies to one of our supported databases, it is way far from critical. It is basically a missing feature and therefore should be part of Drupal 7 and possibly backported to Drupal 6 if simple enough. It might not apply to Drupal 7 given the new database layer, in which case feel free to move back to Drupal 6 as a task. Not a bug.

We could certainly add a range() method to delete or update queries as far as the PHP is concerned, but if Postgres doesn't support that in the first place then I don't know if it's a good idea for us to do so. Damien?

This can be emulated using a subselect (DELETE FROM foo WHERE foo_id IN (SELECT foo_id FROM foo ... LIMIT 5)) on all databases, but isn't natively supported except on MySQL, so this is a won't fix.

You can easily do that on D7 with a subquery in a db_delete() query. Won't fix.

This code results in "PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 1'"

<?php
$result = db_query_range("DELETE FROM {simpleblogroll} WHERE feed_url = :feed_url ORDER BY timestamp ASC",0,1, array(':feed_url' => $feedurl,));
?>

This code results in "PDOException: SQLSTATE[42000]: Syntax error or access violation: 1235 This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery':"
http://dev.mysql.com/doc/refman/5.0/en/subquery-errors.html

<?php
$result = db_query("DELETE FROM {simpleblogroll} WHERE feed_url IN(SELECT feed_url FROM {simpleblogroll} WHERE feed_url = :feed_url ORDER BY timestamp ASC LIMIT 1)", array(':feed_url' => $feedurl,));
?>

Same problem here:

<?php
$subquery = db_select('simpleblogroll','sbr');
$subquery->fields('sbr',Array('feed_url'))
->orderBy('timestamp', 'ASC')
->range(0,1)
->condition('feed_url', $feedurl);
         
db_delete('simpleblogroll')
->condition('feed_url', $subquery, 'IN')
->execute(); 
?>

This code results in "PDOException: SQLSTATE[HY000]: General error: 1093 You can't specify target table 'simpleblogroll' for update" It looks like you can't update the same table which you use in the SELECT part in MYSQL
http://bugs.mysql.com/bug.php?id=6980

<?php
$subquery = db_select('simpleblogroll','sbr');
$subquery->fields('sbr',Array('feed_url'))
->orderBy('timestamp', 'ASC')
->condition('feed_url', $feedurl);
         
db_delete('simpleblogroll')
->condition('feed_url', $subquery, 'IN')
->execute(); 
?>

I tried to do this with a subselect and a subquery on db_delete() and ran into problems with both. See above.

My original goal was to update some D6 code to D7:

<?php
db_query('DELETE FROM {simpleblogroll} '
            ."WHERE feed_url='%s' "
            .'ORDER BY timestamp ASC '
            .'LIMIT 1',
            $feedurl
          );
?>

Unfortunately you cannot use ->condition() with a subselect. See

  /**
   * Helper function: builds the most common conditional clauses.
   *
   * This method can take a variable number of parameters. If called with two
   * parameters, they are taken as $field and $value with $operator having a
   * value of IN if $value is an array and = otherwise.
   *
   * @param $field
   *   The name of the field to check. If you would like to add a more complex
   *   condition involving operators or functions, use where().
   * @param $value
   *   The value to test the field against. In most cases, this is a scalar.
   *   For more complex options, it is an array. The meaning of each element in
   *   the array is dependent on the $operator.
   * @param $operator
   *   The comparison operator, such as =, <, or >=. It also accepts more
   *   complex options such as IN, LIKE, or BETWEEN. Defaults to IN if $value is
   *   an array, and = otherwise.
   *
   * @return QueryConditionInterface
   *   The called object.
   */
  public function condition($field, $value = NULL, $operator = NULL);

You'll probably need to use the where() function.

Also, it appears that your original support request has been addressed in #630054-8: About port to Drupal 7, so I'm going to reset the issue status on this one back to it's prior values.