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.
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.
answered 12 Dec '12, 14:47
You could also potentially use the
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
answered 13 Dec '12, 12:30
LIMIT and OFFSET must be enabled as keywords. SQLAW supports them.
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
answered 12 Dec '12, 21:47