Posts Tagged ‘EXISTS’

Conditional Insert Query Based on Data in Target Table

Posted: June 23, 2013 by Transformer in MS Access, SQL
Tags: ,

Problem Statement: There is a table named Employee which has department wise employee details as given below. We want to insert some new records in this table but a record should not be inserted if it is already there in the table.


Dept	Employee_ID	Gender
IT	E123	          M
IT	E124	          M
IT	E125	          M
IT	E126	          M
IT	E127	          F
IT	E128	          F
HR	E129	          M
HR	E130	          F
HR	E131	          F
HR	E132	          F
HR	E133	          M

Solution: This can be done using the following query where we use EXISTS to check whether or not that record already exists.


INSERT INTO Employee (Dept,Employee_ID,Gender)

   SELECT  TOP 1 'HR' , 'E132','F' 
       FROM
         EMPLOYEE 
  WHERE Not EXISTS 
         (SELECT 1 
             FROM 
               Employee 
          WHERE Dept = 'HR' AND Employee_ID = 'E132'  AND Gender = 'F')

The above query will not insert the record in the table since it already exists.
Here TOP 1 has been used to create a single record to insert otherwise it will return multiple records(No of records in the Employee Table) with the same values.

Advertisements

Problem Statement: There are two tables and we need to get all the records from Table1 that are not in Table2.


   Table1                           Table2
Emp_id	Salary			Emp_id	Name
1	10000			1	Tom
2	23000			2	Rock
4	40000			3	Thor
9	34000				

Excluding Records from Table1 that Exist in Table2

Solution: It can be done in two ways, using Left Join OR using Exists condition. (more…)