Solved

SQL Not exists

Posted on 2006-10-31
8
310 Views
Last Modified: 2012-05-05
I have

SELECT UDT02_ID, UDT09_ID
      FROM DELTEKTC.DELTEKET.ET_LINK29
WHERE UDT02_ID IS NOT NULL
AND UDT09_ID IS NOT NULL
and UDT02_ID LIKE '00401016.0004' + '%'

I then want to check that it does not exists in table below i tried the following
WHERE NOT EXISTS ( SELECT UDT09_ID, UDT02_ID FROM PCAPP.dbo.PCARD_TCL)

but errors

Incorrect syntax near the keyword 'WHERE'.

0
Comment
Question by:TRACEYMARY
  • 4
  • 3
8 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17842145
Hi TRACEYMARY,


SELECT UDT02_ID, UDT09_ID
     FROM DELTEKTC.DELTEKET.ET_LINK29
WHERE UDT02_ID IS NOT NULL
AND UDT09_ID IS NOT NULL
and UDT02_ID LIKE '00401016.0004' + '%'

AND  NOT EXISTS ( SELECT 1 FROM PCAPP.dbo.PCARD_TCL)


Cheers!
0
 
LVL 10

Expert Comment

by:AaronAbend
ID: 17842152
SELECT UDT02_ID, UDT09_ID
     FROM DELTEKTC.DELTEKET.ET_LINK29
WHERE UDT02_ID IS NOT NULL
AND UDT09_ID IS NOT NULL
and UDT02_ID LIKE '00401016.0004' + '%'
and NOT EXISTS ( SELECT UDT09_ID, UDT02_ID FROM PCAPP.dbo.PCARD_TCL)
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 17842173
Or is this what u r looking for

SELECT t1.UDT02_ID, t1.UDT09_ID
     FROM DELTEKTC.DELTEKET.ET_LINK29 t1
WHERE UDT02_ID IS NOT NULL
AND UDT09_ID IS NOT NULL
and UDT02_ID LIKE '00401016.0004' + '%'
and NOT EXISTS ( SELECT 1 FROM PCAPP.dbo.PCARD_TCL WHERE UDT09_ID=t1.UDT02_ID AND UDT02_ID=t1.UDT09_ID )
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 7

Author Comment

by:TRACEYMARY
ID: 17843003
Whats the select 1 for ?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17843018
SELECT '1'  --will return 1 if the criteria matches, you can return any value, *, or any column name
0
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 17843469
Thanks ......i appreciate it
0
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 17844397
Is there a better way to write this

Write to temp table then
   loop around temp

    IF NOT exists (select x, y from table)
        Insert
 
   loop again

I understand it more when its long winded lol
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17847617
>Is there a better way to write this

This is the optimized code,
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

803 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