Issue Details (XML | Word | Printable)

Key: NUCRDBMS-630
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Andy Jefferson
Reporter: Andy Jefferson
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
DataNucleus Store RDBMS

SQLServer doesn't support use of table aliases in bulk DELETE statement

Created: 10/Jan/13 09:39 AM   Updated: 19/Jan/13 09:19 AM   Resolved: 10/Jan/13 10:10 AM
Component/s: Queries
Affects Version/s: 2.2.4, 3.0.10, 3.1.3, 3.2.0.m2
Fix Version/s: 3.1.4, 3.2.0.m3


 Description  « Hide
SQLServer doesn't seem to support any half sensible syntax for bulk DELETE. See http://msdn.microsoft.com/en-us/library/ms189835.aspx

Solution would be maybe to omit the ALIAS if no WHERE clause, and update it to generate something like
DELETE FROM MYTABLE
WHERE TBLID IN (SELECT TBLID FROM MYTABLE T0 WHERE T0.SalesYTD > 2500000.00);
when there is a WHERE clause.

Or maybe there is some other better solution that could be applied to other datastore adapters

Sort Order: Ascending order - Click to sort in descending order
Andy Jefferson added a comment - 10/Jan/13 10:10 AM
SVN trunk and branches/3.1 now uses (for SQLServer/Sybase)

DELETE t1 FROM MYTABLE t1

which allegedly works with TSQL as per
http://stackoverflow.com/questions/7042128/delete-rows-from-sql-server-with-where-statement-from-different-tables

Sunil added a comment - 10/Jan/13 11:07 AM
Where clause was also not working with this query. Please comment that also if it is fixed.

Andy Jefferson added a comment - 10/Jan/13 11:14 AM
How can anyone comment about some WHERE clause not working when nowhere have you actually defined what query you're using? A JPQL query string is a minimum requirement to define any problem.