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

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

Andy Jefferson made changes - 10/Jan/13 10:10 AM
Field Original Value New Value
Status Open [ 1 ] Resolved [ 5 ]
Assignee Andy Jefferson [ andy ]
Fix Version/s 3.1.4 [ 11811 ]
Fix Version/s 3.2.0.m3 [ 11840 ]
Resolution Fixed [ 1 ]
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.

Andy Jefferson made changes - 19/Jan/13 09:19 AM
Status Resolved [ 5 ] Closed [ 6 ]