We help IT Professionals succeed at work.

SQL Not exists

TRACEYMARY
TRACEYMARY asked
on
Medium Priority
338 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'.

Comment
Watch Question

AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
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!
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)
Database Consultant
CERTIFIED EXPERT
Top Expert 2009
Commented:
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 )

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Whats the select 1 for ?
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
SELECT '1'  --will return 1 if the criteria matches, you can return any value, *, or any column name

Author

Commented:
Thanks ......i appreciate it

Author

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
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
>Is there a better way to write this

This is the optimized code,
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.