Why id delete with where clause deleting all the rows?

Question

I am trying to delete some rows from table A with the where condition referring to another table. So basically deleting rows from table A also present in table B.

Following is the query I'm using:

delete Table_A from Table_A t1
join table_B t2
on t1.id = t2.id
where cndt_1 and cndt_2 and .... and cndt 7
-- The where conditions are just to check field values are same for multiple -- cols of the 2 tables

This query is deleting every single row present in table and I can't seem to understand why. I have tried the same query with left join too. Same result. I tried adding the where conditions just under on and also paired with where. In every case, it is deleting all the rows

What Data Base Management System are you using? Please tag your question with the specific DBMS. I am not aware of any that allow tablenames between Delete and From. — Chris Maurer
– Chris Maurer, Commented Feb 24, 2023 at 5:26
If conditions in "where cndt_1 and cndt_2 and .... and cndt 7" are true, query will delete all rows from table_A, presented in table_B. For your question, cndt_1 an so on mast be explained. — ValNik
– ValNik, Commented Feb 24, 2023 at 6:49
Your conditions aren't as restrictive as you believed. When we cannot see either the actual conditions, nor the data, nor any kind of narrative stating what you were trying to achieve it's a little tricky to see any way we can help you, lacking those details. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Feb 24, 2023 at 8:32

gotqn · Accepted Answer · 2023-02-24 06:47:49Z

1

Well, this is very simple to check. Just run the following query:

SELECT *
from Table_A t1
join table_B t2
     on t1.id = t2.id
where cndt_1 and cndt_2 and .... and cndt 7

and you will get the rows you are going to delete. Then, you can remove some of your conditions or change them in order to filter the rows further.

answered Feb 24, 2023 at 6:47

gotqn

43.9k47 gold badges167 silver badges256 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nkifor · Accepted Answer · 2023-02-24 09:15:34Z

0

I recommend this one:

This deletes

DELETE
From Table T
Join 
OtherTable Ot
On
T.id=Ot.id
Where 
"here are your specific conditions from both tables which defines records to delete . Eg. T.name='Titanic'"

This updates:

Update T
Set T.column = "your value you want to change"
From Table T
Join 
OtherTable Ot
On
T.id=Ot.id
Where 
"here are your specific conditions from both tables. Eg. T.name='Titanic'"

answered Feb 24, 2023 at 9:15

Nkifor

381 silver badge5 bronze badges

Collectives™ on Stack Overflow

Why id delete with where clause deleting all the rows?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related