The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

I previously asked about support for the LIMIT statement.

Is the LIMIT statement supported in DELETE queries?

I tried this, but get a SQL Anywhere syntax error (the statement does work for MySQL):

DELETE FROM users WHERE UserId = '9' LIMIT 1

This works for SQL Anywhere and MySQL:

DELETE FROM users WHERE UserId = '9'

I'm trying to use an ORM library where the SQL statements are written by the library not me. If the LIMIT statement isn't supported, I won't be able to use the ORM library.

asked 10 Dec '12, 05:08

MMacdonald's gravatar image

MMacdonald
1766617
accept rate: 0%

edited 15 Mar '13, 21:02

Mark%20Culp's gravatar image

Mark Culp
22.3k9129262


In contrast to the SELECT statement, there seems to be no support for the LIMIT clause in a DELETE or UPDATE statement (cf. DCX DELETE Statement").

permanent link

answered 10 Dec '12, 05:27

Reimer%20Pods's gravatar image

Reimer Pods
4.1k324278
accept rate: 12%

1

...unless you do the delete with joining against a derived table or using an EXISTS/IN subquery which both themselves could use the LIMIT clause, such as (untested):

DELETE FROM users WHERE UserID = '9'
   AND UserID IN (SELECT UserID from users ... LIMIT 1);

Not a very reasonable sample, FWIW...

(10 Dec '12, 05:39) Volker Barth

Is this a planned feature?

(10 Dec '12, 06:17) MMacdonald

SQL Anywhere supports TOP and FIRST for both DELETE and UPDATE statements and has done so for a long time (at least Version 5). So

DELETE FIRST FROM users where UserID = '9'

will work. When LIMIT was added for MySQL compatibility it may be that it was only added for SELECT statements and query expressions - I cannot recall. It would be easy to implement LIMIT for update DML statements but I have no idea when that might make the feature list - someone in Engineering will have to respond to that.

What ORM library are you using? You may be able to modify the library so that the DELETE statement is modified to use TOP instead of LIMIT.

permanent link

answered 12 Dec '12, 14:47

Glenn%20Paulley's gravatar image

Glenn Paulley
10.7k568104
accept rate: 43%

Wouldn't you need an order by clause? Otherwise how do we decide which one is "first"?

(12 Dec '12, 18:53) Graeme Perrow
Replies hidden

Yes, the current docs tell for FIRST / TOP n:

When specifying these clauses, an ORDER BY clause is required to order the rows in a meaningful manner.

ORDER BY for DELETE has been introduced in v10. (For UPDATE, it was introduced a lot earlier.) - So with older versions, I'm not sure what the effect would be (and if it's deterministic at all)...

(13 Dec '12, 04:04) Volker Barth

You could also potentially use the set rowcount T-SQL option to limit rows when deleting:

create table t1 ( c1 int );

insert into t1 values ( 1 );
insert into t1 values ( 2 );
insert into t1 values ( 2 );
insert into t1 values ( 2 );
insert into t1 values ( 3 );
insert into t1 values ( 4 );
commit;

set rowcount 1;
delete from t1 where c1 = 2;
set rowcount 0;   -- Don't forget to turn it off!

select * from t1;

But it sounds like you really want a single SQL statement to be generated for your FuelPHP ORM that is compatible with SQL Anywhere.

I took a look through the Fuel ORM code and saw that the entire dialect is only geared towards only MySQL. While we're trying to build MySQL-compatible syntax features into SQL Anywhere, these are not yet fully interchangeable.

I have opened an enhancement request (#727082) for the LIMIT keyword to be also supported with the UPDATE and DELETE statements to have more compatible syntax with MySQL, to be considered in a future version of SQL Anywhere. Thank you for the enhancement request.

permanent link

answered 13 Dec '12, 12:30

Jeff%20Albion's gravatar image

Jeff Albion
10.7k171174
accept rate: 24%

LIMIT and OFFSET must be enabled as keywords. SQLAW supports them.

Help says:

The LIMIT keyword is disabled by default. Use the reserved_keywords option to enable the LIMIT keyword.

The help, howerver, does not say whether the LIMIT OFFSET also works for delete. Only TOP and START AT are mentioned.

Give it a try

Martin

permanent link

answered 12 Dec '12, 21:47

Tinu's gravatar image

Tinu
76338
accept rate: 0%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×46
×15

question asked: 10 Dec '12, 05:08

question was seen: 4,919 times

last updated: 15 Mar '13, 21:02