Solved

SQL Not exists

Posted on 2006-10-31
8
312 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

685 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