CODEWITHSUNDEEP
sybase
user726720
user726720

Reputation: 1237

Sybase Delete command not working as expected

I wrote this simple commands to test the delete command but found some discrepancies with it:

delete table1 from table1,table2
where table1.col1 = table2.col1
and table1.col2= table2.col2

In table1 I have 272768 rows, table2 I have 1380 rows. Now I need to remove these 1380 rows available in table2 from table1. But to my surprise it removed 2234 rows from table1 after running the above script. The expected removal should be only 1380 rows. Is there anything I can do to optimize this ?

Upvotes: 0

Views: 3242

Answers (2)

Jonathan Leffler
Jonathan Leffler

Reputation: 754090

If you run the query:

SELECT COUNT(*)
  FROM table1 JOIN table2
    ON table1.col1 = table2.col1 AND table1.col2 = table2.col2

you will find that the 1380 rows in table2 match 2234 rows in table1, so the DELETE is doing exactly what it should do.

That query is the preferred form; you can use the antiquated notation without explicit joins:

SELECT COUNT(*)
  FROM table1, table2
 WHERE table1.col1 = table2.col1 AND table1.col2 = table2.col2

But you should be using the explicit JOIN notation in anything you write; you should only need to know about the comma-separated list in the FROM clause for understanding old queries written in another millennium.

Upvotes: 0

Robert
Robert

Reputation: 25753

Try this way:

delete from table1 
from  table2
where table1.col1 = table2.col1
and table1.col2= table2.col2

or

delete from table1 
where exists 
(
  select 1
    from table2 
  where table1.col1 = table2.col1
    and table1.col2= table2.col2

)

Upvotes: 1

Related Questions