on 2012 Dec 10 5:08 AM
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.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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)...
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").
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
...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...
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
69 | |
10 | |
9 | |
6 | |
6 | |
6 | |
6 | |
5 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.