CODEWITHSUNDEEP
sql
Noam650
Noam650

Reputation: 113

DELETE row from different tables using SQL

here are the values of the two tables and the relation between them

I'm trying to delete all the rows in tblProInfo,tblOrderAA where tblProInfo.proInfoScienceName='shalosh'

Here is the command I wrote. I get a message that it couldn't generate my query.

DELETE tblOrderAA.*,  tblProInfo.*
FROM tblProInfo INNER JOIN tblOrderAA ON tblProInfo.proInfoSerialNum = tblOrderAA.orderAASerialPro
WHERE (((tblProInfo.proInfoScienceName)='shalosh'

Upvotes: 0

Views: 718

Answers (3)

Romil Kumar Jain
Romil Kumar Jain

Reputation: 20745

DELETE a1,  a2
FROM tblProInfo as a1 INNER JOIN tblOrderAA as  a2
ON a1.proInfoSerialNum = a2.orderAASerialPro
WHERE a1.proInfoScienceName='shalosh'

The syntax is correct as per msaccess but doesn't delete records. Access delete queries cannot delete from more than one table at a time. This is an Access limitation.

Refer here to know about deleting records from multiple tables in MS-Access

If both tables are connected and referential integrity set such that deletes are cascaded then you can cause records to be deleted from both tables at the same time by simply deleting the record on the One side of the link.

In Sql Server:
The concept of deleting rows from multiple tables in a single delete statement cannot be done in sql server. There is the concept of triggers on the tables that do deletes in a cascading style.

Upvotes: 3

Arion
Arion

Reputation: 31239

You have to separate the statements. You can not delete from two tables with one query

DELETE FROM tblOrderAA
WHERE tblOrderAA.orderAASerialPro IN
(
    SELECT orderAASerialPro 
    FROM tblProInfo WHERE tblProInfo    
    tblProInfo.proInfoScienceName='shalosh'
)

DELETE FROM orderAASerialPro
WHERE tblProInfo.proInfoScienceName='shalosh'

Upvotes: 0

Barry Kaye
Barry Kaye

Reputation: 7761

On this line you have 3 opening brackets and 1 closing bracket:

WHERE (((tblProInfo.proInfoScienceName)='shalosh'

Upvotes: 0

Related Questions