Bug #94270 for SQL-Abstract-More: Allow for -order

Thu Mar 27 19:02:06 2014 rsimoes [...] cpan.org - Ticket created

Subject:

Allow for -order_by in UPDATE and DELETE queries

I was surprised to discover that one cannot provide an -order_by clause to an UPDATE query. Would it be simple to allow for it?

Sat Mar 29 04:45:23 2014 laurent.dami [...] free.fr - Correspondence added

Subject:	Re: [rt.cpan.org #94270] Allow for -order_by in UPDATE and DELETE queries
Date:	Sat, 29 Mar 2014 10:45:08 +0200
To:	bug-SQL-Abstract-More [...] rt.cpan.org
From:	Laurent Dami <laurent.dami [...] free.fr>

Le 28.03.2014 01:02, Richard Simões via RT a écrit : Show quoted text

> Thu Mar 27 19:02:06 2014: Request 94270 was acted upon. > Transaction: Ticket created by rsimoes > Queue: SQL-Abstract-More > Subject: Allow for -order_by in UPDATE and DELETE queries > Broken in: (no value) > Severity: (no value) > Owner: Nobody > Requestors: rsimoes@cpan.org > Status: new > Ticket <URL: https://rt.cpan.org/Ticket/Display.html?id=94270 > > > > I was surprised to discover that one cannot provide an -order_by clause to an UPDATE query. Would it be simple to allow for it?

SQL has no ORDER BY clause for UPDATE and DELETE statements, so it wouldn't make sense to support it in SQL-Abstract-More. Or do you know any DBMS that accepts this clause ?

Sat Mar 29 04:45:23 2014 The RT System itself - Status changed from 'new' to 'open'

Sat Mar 29 05:42:20 2014 rsimoes [...] cpan.org - Correspondence added

On Sat Mar 29 02:45:23 2014, laurent.dami@free.fr wrote: Show quoted text

> Le 28.03.2014 01:02, Richard Simões via RT a écrit :

> > Thu Mar 27 19:02:06 2014: Request 94270 was acted upon. > > Transaction: Ticket created by rsimoes > > Queue: SQL-Abstract-More > > Subject: Allow for -order_by in UPDATE and DELETE queries > > Broken in: (no value) > > Severity: (no value) > > Owner: Nobody > > Requestors: rsimoes@cpan.org > > Status: new > > Ticket <URL: https://rt.cpan.org/Ticket/Display.html?id=94270 > > > > > > > I was surprised to discover that one cannot provide an -order_by > > clause to an UPDATE query. Would it be simple to allow for it?

> SQL has no ORDER BY clause for UPDATE and DELETE statements, so it > wouldn't make sense to support it in SQL-Abstract-More. Or do you know > any DBMS that accepts this clause ?

MySQL allows for ORDER BY in both UPDATE and DELETE, as a way of controlling which rows are affected when there is also a LIMIT clause: http://dev.mysql.com/doc/refman/5.6/en/update.html http://dev.mysql.com/doc/refman/5.6/en/delete.html

Sat Mar 29 10:17:03 2014 laurent.dami [...] free.fr - Correspondence added

Subject:	Re: [rt.cpan.org #94270] Allow for -order_by in UPDATE and DELETE queries
Date:	Sat, 29 Mar 2014 16:16:50 +0200
To:	bug-SQL-Abstract-More [...] rt.cpan.org, Peter Rabbitson <ribasushi [...] cpan.org>
From:	Laurent Dami <laurent.dami [...] free.fr>

Le 29.03.2014 11:42, Richard Simões via RT a écrit : Show quoted text

> MySQL allows for ORDER BY in both UPDATE and DELETE, as a way of > controlling which rows are affected when there is also a LIMIT clause: > http://dev.mysql.com/doc/refman/5.6/en/update.html > http://dev.mysql.com/doc/refman/5.6/en/delete.html

Hum ... OK, if they support it, so we should support it. It's really weird, though; I can't think of good cases to use this feature, and I find the example from the MySQL doc not really convincing. Anyway, the problem is that SQL-Abstract-More is just a translator into SQL-Abstract, and SQL-Abstract doesn't have an $order_by argument for delete() and update(). I cc to Ribasushi to see if he is open to an evolution of SQL-Abstract; then I could in turn adapt SQL-Abstract-More. If he doesn't agree, the alternative is to code the whole thing within SQL-Abstract-More.

Wed Apr 02 10:00:12 2014 rsimoes [...] cpan.org - Correspondence added

A typical use case combines ORDER BY with LIMIT, so as to update or delete rows by an ordinal criterion in a manner that may be more performant than a self-join. E.g., -- Deactivate the ten oldest widgets: UPDATE widgets SET active = 0 WHERE active = 1 ORDER BY id ASC LIMIT 10; On Sat Mar 29 08:17:03 2014, laurent.dami@free.fr wrote: Show quoted text

> Le 29.03.2014 11:42, Richard Simões via RT a écrit :

> > MySQL allows for ORDER BY in both UPDATE and DELETE, as a way of > > controlling which rows are affected when there is also a LIMIT clause: > > http://dev.mysql.com/doc/refman/5.6/en/update.html > > http://dev.mysql.com/doc/refman/5.6/en/delete.html

> Hum ... OK, if they support it, so we should support it. It's really > weird, though; I can't think of good cases to use this feature, and I > find the example from the MySQL doc not really convincing. > > Anyway, the problem is that SQL-Abstract-More is just a translator into > SQL-Abstract, and SQL-Abstract doesn't have an $order_by argument for > delete() and update(). I cc to Ribasushi to see if he is open to an > evolution of SQL-Abstract; then I could in turn adapt SQL-Abstract-More. > If he doesn't agree, the alternative is to code the whole thing within > SQL-Abstract-More.

Sun Apr 20 21:31:16 2014 DAMI [...] cpan.org - Correspondence added

Le Jeu 27 Mar 2014 19:02:06, rsimoes a écrit : Show quoted text

> I was surprised to discover that one cannot provide an -order_by > clause to an UPDATE query. Would it be simple to allow for it?

Fixed in v1.21

Sun Apr 20 21:31:16 2014 DAMI [...] cpan.org - Status changed from 'open' to 'resolved'

Sun Apr 20 21:31:17 2014 DAMI [...] cpan.org - Fixed in 1.21 added

Bug #94270 for SQL-Abstract-More: Allow for -order_by in UPDATE and DELETE queries