• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 324
  • Last Modified:

SQL Not exists

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
TRACEYMARY
Asked:
TRACEYMARY
  • 4
  • 3
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
AaronAbendCommented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
TRACEYMARYAuthor Commented:
Whats the select 1 for ?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT '1'  --will return 1 if the criteria matches, you can return any value, *, or any column name
0
 
TRACEYMARYAuthor Commented:
Thanks ......i appreciate it
0
 
TRACEYMARYAuthor Commented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
>Is there a better way to write this

This is the optimized code,
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now