Problem with my SQL Delete rule

D

Dirk Goldgar

Sylvain Bissonnette said:
Hi,

I have a problem to delete a record from my table "rules" If I only
make a select it's work I got back one record, but with the delete all my
table is deleted.
Here is my SQL query.

DELETE *
FROM Rules
WHERE EXISTS (
SELECT Rules.*
FROM Device INNER JOIN Rules ON Device.DeviceName=Rules.Name1 Or
Device.DeviceName=Rules.Name2 Or Device.DeviceName=Rules.Name3
WHERE Device.DeviceID=519);
Click to expand...


After taking a backup, try this:

DELETE Rules.*
FROM Device INNER JOIN Rules ON
Device.DeviceName=Rules.Name1 Or
Device.DeviceName=Rules.Name2 Or
Device.DeviceName=Rules.Name3
WHERE Device.DeviceID=519;
 
S

Sylvain Bissonnette

Hi,

I have a problem to delete a record from my table "rules" If I only
make a select it's work I got back one record, but with the delete all my
table is deleted.
Here is my SQL query.

DELETE *
FROM Rules
WHERE EXISTS (
SELECT Rules.*
FROM Device INNER JOIN Rules ON Device.DeviceName=Rules.Name1 Or
Device.DeviceName=Rules.Name2 Or Device.DeviceName=Rules.Name3
WHERE Device.DeviceID=519);


Thanks for all your help
Sylvain Bissonnette
 
J

John Spencer

You could try the following

DELETE
FROM RULES
WHERE RULES.Name1 in
(SELECT DeviceName FROM Device WHERE DeviceID = 519)
OR RULES.Name2 in
(SELECT DeviceName FROM Device WHERE DeviceID = 519)
OR RULES.Name3 in
(SELECT DeviceName FROM Device WHERE DeviceID = 519)

Your exists clause was always going to return TRUE for EVERY RECORD in Rules.
You should be able to write it this way as a correlated subquery that would
work as expected

DELETE *
FROM Rules
WHERE EXISTS
(SELECT *
FROM Device
WHERE (Device.DeviceName=Rules.Name1 Or
Device.DeviceName=Rules.Name2 Or
Device.DeviceName=Rules.Name3)
AND Device.DeviceID=519)


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
D

Dirk Goldgar

Sylvain Bissonnette said:
Hi Dirk


Could not delete from the specify table

Any other idea?
Click to expand...


Interesting. A simple test worked for me, so the difference may have to do
with the indexes, or the presence of a primary key in one or both tables. I
see you got a workable solution from John Spencer, so I won't pursue it
further.
 
S

Sylvain Bissonnette

Hi Dirk
After taking a backup, try this:

DELETE Rules.*
FROM Device INNER JOIN Rules ON
Device.DeviceName=Rules.Name1 Or
Device.DeviceName=Rules.Name2 Or
Device.DeviceName=Rules.Name3
WHERE Device.DeviceID=519;
Click to expand...

Could not delete from the specify table

Any other idea?

Sylvain
 
S

Sylvain Bissonnette

Hi John,

Hey it's work, You can not imagin how I'm greatfull for this help!!

Thanks a lot
Sylvain Bissonnette
 
S

Sylvain Bissonnette

Hi John

Could you help me again, those SQL query are out of my knowleage I got
the select with this one but I don't figure out how to
delete the "selected" data...

SELECT DeviceType.DeviceTypeID, Rules.*
FROM (Device INNER JOIN (DeviceClass INNER JOIN DeviceType ON
DeviceClass.DeviceClassID=DeviceType.DeviceClassID) ON
Device.DeviceTypeID=DeviceType.DeviceTypeID) INNER JOIN Rules ON
(Device.DeviceName=Rules.DoName) Or (Device.DeviceName=Rules.Name3) Or
(Device.DeviceName=Rules.Name2) Or (Device.DeviceName=Rules.Name1)
WHERE (((DeviceType.DeviceTypeID)=20));

Thanks
Sylvain Bissonnette
 
J

John Spencer

Assumption is that you want to delete the records in RULES table.
Perhaps the following, but I WOULD back up your data before I even tried
this.

If it fails, then try to get just one section of the Where clause to
work and then once you get that to work you can duplicate it for the
other three sections


DELETE
FROM RULES
WHERE
Rules.DoName in
(SELECT Device.DeviceName
FROM Device
WHERE DeviceType.DeviceTypeID=20)
OR Rules.Name1 in
(SELECT Device.DeviceName
FROM Device
WHERE DeviceType.DeviceTypeID=20)
Or Rules.Name2 in
(SELECT Device.DeviceName
FROM Device
WHERE DeviceType.DeviceTypeID=20)
Or Rules.Name3 in
(SELECT Device.DeviceName
FROM Device
WHERE DeviceType.DeviceTypeID=20)


This may get more records than you want if there has to be related
records in the DeviceClass and DeviceType tables for records in the
Device table.

If so, you will need to add the tables back in to the where
clause like so.

WHERE Rules.DO in
(SELECT Device.DeviceName
FROM (Device INNER JOIN (DeviceClass INNER JOIN DeviceType ON
DeviceClass.DeviceClassID=DeviceType.DeviceClassID) ON
Device.DeviceTypeID=DeviceType.DeviceTypeID)
WHERE DeviceType.DeviceTypeID=20)



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
S

Sylvain Bissonnette

Hi,

Thanks again for your help, it's work at 90%, It ask me to enter the
value of DeviceType.DeviceTypeID if I enter 20 it's work
but can I do something to don't have to enter this value?

Sylvain
 
J

John Spencer

My error. I should have said Device.DeviceTypeId and not DeviceType.DeviceTypeID.

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

DELETE
FROM RULES
WHERE
Rules.DoName in
(SELECT Device.DeviceName
FROM Device
WHERE Device.DeviceTypeID=20)
OR Rules.Name1 in
(SELECT Device.DeviceName
FROM Device
WHERE Device.DeviceTypeID=20)
Or Rules.Name2 in
(SELECT Device.DeviceName
FROM Device
WHERE Device.DeviceTypeID=20)
Or Rules.Name3 in
(SELECT Device.DeviceName
FROM Device
WHERE Device.DeviceTypeID=20)



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
S

Sylvain Bissonnette

Hi John,

It's work! exactly like I had expected, you are the man!, I will sleep
bether this night

Again you help was really apreciated,

Yours,
Sylvain Bissonnette
 
S

Sylvain Bissonnette

Hi John,

Excuse me to disturb you again, I had forgot something to ask you (if I
can) for the last time could you

help me with this select query. I want to transfore it in a delete query



Thanks for all your help,

Sylvain Bissonnette



SELECT DeviceClass.DeviceClassID, Rules.*

FROM (Device INNER JOIN (DeviceClass INNER JOIN DeviceType

ON DeviceClass.DeviceClassID=DeviceType.DeviceClassID)

ON Device.DeviceTypeID=DeviceType.DeviceTypeID)

INNER JOIN Rules ON (Device.DeviceName=Rules.DoName) Or

(Device.DeviceName=Rules.Name3) Or

(Device.DeviceName=Rules.Name2) Or

(Device.DeviceName=Rules.Name1)

WHERE (((DeviceType.DeviceClassID)=7));
 
J

John Spencer

I'll try. But this is the last one. You should have enough examples to
figure this out on your own. FIll in the (...) appropriately.

DELETE
FROM RULES
WHERE Rules.DoName in
(SELECT Device.DeviceName
FROM Device INNER JOIN DeviceType
ON Device.DeviceTypeID= DeviceType.DeviceTypeID
WHERE DeviceType.DeviceClassID = 7)
OR Rules.Name1 in
(...)
OR Rules.Name2 in
(...)
OR Rules.Name3 in
(...)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

delete query - could not delete from specified table 4
Manage Rules & Alerts window wont stay open 4
Delete query - delete from specified tables error 4
delete records in one table that occur in another table 0
Show only free IP addresses when selecting address to certain device? 4
Problem deleting records 1
Launching Report from Form Only Shows Report for First Record 5
setFocus & inner join query glitch... 2

Top