SQL Query: Delete record if it is exist in sub query

Scenario:
I have created a table tGymReg where I import all the current students from the live updated table tStudCYAll. When a student leave tStudCYAll table get updated but my table tGymReg does not get updated. So I need to delete all the old student record.

Solution:
In this case the student who has left will not be available in tStudCYAll but it will be available in tGymReg.
Query:

DELETE * FROM tGymReg WHERE tGymReg.AdmissionNumber IN
(SELECT tGymReg.AdmissionNumber
FROM tGymReg LEFT JOIN dbo_vwStudCYAll ON tGymReg.AdmissionNumber = dbo_vwStudCYAll.AdmissionNumber
WHERE (((dbo_vwStudCYAll.AdmissionNumber) Is Null)));

DELETE * FROM `products` WHERE `products_id` IN
(SELECT p.`products_id`
   FROM `products` p, `products_suppliers` ps
   WHERE p.`products_id` = ps.`products_id`
   AND (p.`products_id` 215 AND p.`products_id` 305))

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s