Translating Queries from MS Access to SQL Server

ms accesssql server

I have a legacy query from years ago that was written for an MS Access backend:

    DELETE category.*
    FROM category
    LEFT JOIN product_category ON category.id = product_category.categoryid
    WHERE product_category.categoryid IS NULL
      AND category.id IN
        (SELECT cat.id
         FROM category AS cat
         LEFT JOIN category AS cat_par ON cat_par.category_parent = cat.id
         WHERE cat_par.category_parent IS NULL
           AND cat.category_parent != 0
           AND cat.link IS NULL);

My system was changed to SQL Server and this query no longer runs.
I have tested the subquery (in the parenthesis) separately and that works as expected. Just no records are getting deleted.

I have tried changing the first two lines to read DELETE FROM category (the only syntax difference I was able to look up) but still no deletion occurs.

Best Answer

I think Binaya Regmi is correct. When deleting in SQL-Server explicitly name the table you want to delete the rows from - especially necessary when using a join.

So in your case change the top row from

DELETE category.*

DELETE category

Related Solutions

Sql-server – Selecting values that are not associated with one side of a many to many relationship

You need all combinations of Category and Value (a CROSS JOIN) except those that appear in the CategoryValue. This can be done with a LEFT JOIN / IS NULL

SELECT 
    c.CategoryID, v.ValueID, v.ValueName
FROM 
    Category AS c
  CROSS JOIN
    Value AS v
  LEFT JOIN
    CategoryValue AS cv
      ON  cv.CategoryID = c.CategoryID
      AND cv.ValueID = v.ValueID
WHERE
   cv.CategoryID IS NULL ;

or a NOT EXISTS:

SELECT 
    c.CategoryID, v.ValueID, v.ValueName
FROM 
    Category AS c
  CROSS JOIN
    Value AS v
WHERE
   NOT EXISTS
     ( SELECT *
       FROM CategoryValue AS cv
       WHERE cv.CategoryID = c.CategoryID
         AND cv.ValueID = v.ValueID 
     ) ;

or an EXCEPT solution:

SELECT 
    c.CategoryID, v.ValueID, v.ValueName
FROM 
    Category AS c
  CROSS JOIN
    Value AS v

EXCEPT

SELECT 
    cv.CategoryID, v.ValueID, v.ValueName
FROM 
    CategoryValue AS cv
  JOIN
    Value AS v
      ON  cv.ValueID = v.ValueID ;

Sql-server – Deleting Record from Linked Table Slow in MS Access

It could be a mechanism within MS Access when it comes to the linked table. How ???

MSAccess sees a linked table and you issue this:

DELETE FROM [mytable];

This will not run over to SQL Server and do a deletion of all records in a single transaction. What MSAccess does is pick up every PRIMARY KEY and issues an individual DELETE FROM [mytable] WHERE pk = ???, probably as individual transactions (explains the delay before the confirm). I remembered this wonky behavior going back 15 years because I saw this happen when having linked tables to SQL Server, MySQL, and Oracle.

If this is still what MS Access does to a linked table, you are better off connecting to SQL Server via isql and issuing the DELETE on the server side, especially if there are a lot of rows to delete. At the very least, you should minimize executing bulk operations against SQL Server from MSAccess.

Although I have never worked with it, MSAccess can allow "pass through" queries where you issue a SQL Command and the ODBC Driver simply passes the query to the Server Side for parsing and execution rather that trying to parse and execute the SQL the "ODBC" way.

If things have changed over the years with linked tables, I'll leave it to the SQL Server community to address this if I am way off base.

Best Answer

Related Solutions

Sql-server – Selecting values that are not associated with one side of a many to many relationship

Sql-server – Deleting Record from Linked Table Slow in MS Access

Related Question