|
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. |
|
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"). 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. 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:
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 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 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 |
