Solved

optimizing a sql syntax

Posted on 2011-03-06
5
256 Views
Last Modified: 2012-05-11
I have few tables under the database: CRMDB.
The table books is very huge, almost 13712821 rows [aprox].
I feel that the following sysntax would be very costly:
delete from CRMDB.books where custId not in (select custId from CRMDB.customers);

Is there any way to overcome the costly not in command??
0
Comment
Question by:pvinodp
  • 2
  • 2
5 Comments
 
LVL 23

Accepted Solution

by:
Rajkumar Gs earned 200 total points
ID: 35053747
Backup database and try these alternative queries
DELETE b.*
FROM    CRMDB.books b
        LEFT JOIN CRMDB.customers c ON b.custId = c.custId
WHERE   c.custId IS NULL

Open in new window


DELETE  CRMDB.books
WHERE   NOT EXISTS ( SELECT *
                     FROM   CRMDB.customers
                     WHERE  CRMDB.books.custId = CRMDB.customers.custId ) ;

Open in new window

0
 
LVL 40

Assisted Solution

by:Sharath
Sharath earned 300 total points
ID: 35053806
Instead of * you can go with 1 in the sub-query.
DELETE  CRMDB.books
WHERE   NOT EXISTS ( SELECT 1
                     FROM   CRMDB.customers
                     WHERE  CRMDB.books.custId = CRMDB.customers.custId );

Open in new window

0
 

Author Comment

by:pvinodp
ID: 35054112

I tried and getting this error:

delete from omcr.HistoricalAlarmNotification WHERE   NOT EXISTS ( select 1 from omcr.HistoricalAlarmLifecycle where omcr.HistoricalAlarmNotification.halcid omcr.HistoricalAlarmLifecycle.halcid );

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'omcr.HistoricalAlarmLifecycle.halcid )' at line 1
0
 
LVL 40

Expert Comment

by:Sharath
ID: 35054155
you missed =
DELETE FROM omcr.HistoricalAlarmNotification 
      WHERE NOT EXISTS (SELECT 1 
                          FROM omcr.HistoricalAlarmLifecycle 
                         WHERE omcr.HistoricalAlarmNotification.halcid = omcr.HistoricalAlarmLifecycle.halcid);

Open in new window

0
 

Author Closing Comment

by:pvinodp
ID: 35125840
I thank you all for your inputs
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now