Delete query help

Question

Post reply

Delete query help

David Fullerton-463561

SSC-Addicted

Points: 437
More actions
January 27, 2009 at 7:47 am

#199295

SQL 2005
I need to delete the record from RawImport table if both rehire_dates are null. What do I need to do to accomplish this?
DELETE FROM [ADIM_ASSOCIATE_CONTRACTOR_RawImport]
WHERE EMPLOYEE_NUMBER IN
(SELECT a.EMPLOYEE_NUMBER FROM dbo.ADIM_ASSOCIATE_CONTRACTOR a JOIN dbo.ADIM_ASSOCIATE_CONTRACTOR_RawImport b
ON a.[EMPLOYEE_NUMBER] = b.[EMPLOYEE_NUMBER]
AND a.[ASSOCIATE_CONTRACTOR_FLAG] = b.[ASSOCIATE_CONTRACTOR_FLAG]
AND a.[DOM_INTL_IN] = b.[DOM_INTL_IN]
AND a.[CFC_NETWORK_ID] = b.[CFC_NETWORK_ID]
AND a.[NAME_PREFIX] = b.[NAME_PREFIX]
AND a.[SUFFIX] = b.[SUFFIX]
AND a.[HIRE_DATE] = cast(b.[HIRE_DATE] as datetime)
AND cast(nullif(a.[REHIRE_DATE], '')as datetime) = cast(nullif(b.[REHIRE_DATE], '') as datetime)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Abhijit More SSCertifiable Points: 6897 More actions · Answer 1

Why you are going for so many joins, you can achieve this by simple query...

DELETE b

FROM ADIM_ASSOCIATE_CONTRACTOR_RawImport b

JOIN ADIM_ASSOCIATE_CONTRACTOR a ON a.EMPLOYEE_NUMBER = b.EMPLOYEE_NUMBER AND a.[REHIRE_DATE] = b.[REHIRE_DATE]

Abhijit - http://abhijitmore.wordpress.com

David Fullerton-463561 SSC-Addicted Points: 437 More actions · Answer 2

Yes your solution is a start although it still does not adress the issue that I am having.

The data type in RawImport table is varchar and contains records without dates.

The data type in the destination table is Datetime with null values for dates without.

I need to be able to match the varchar coumn wich is blank to the null date in the destination table.

I can certainly incorporate the better code if I new how to handle the matching of the blank date and null date.

Your help is appreciated.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Use the WHERE clause instead?

DELETE b

FROM dbo.ADIM_ASSOCIATE_CONTRACTOR_RawImport b

INNER JOIN dbo.ADIM_ASSOCIATE_CONTRACTOR a

ON a.[EMPLOYEE_NUMBER] = b.[EMPLOYEE_NUMBER]

AND a.[ASSOCIATE_CONTRACTOR_FLAG] = b.[ASSOCIATE_CONTRACTOR_FLAG]

AND a.[DOM_INTL_IN] = b.[DOM_INTL_IN]

AND a.[CFC_NETWORK_ID] = b.[CFC_NETWORK_ID]

AND a.[NAME_PREFIX] = b.[NAME_PREFIX]

AND a.[SUFFIX] = b.[SUFFIX]

AND a.[HIRE_DATE] = cast(b.[HIRE_DATE] AS DATETIME)

WHERE a.[REHIRE_DATE] IS NULL

AND (b.[REHIRE_DATE] IS NULL OR b.[REHIRE_DATE] = '')

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

David Fullerton-463561 SSC-Addicted Points: 437 More actions · Answer 4

David Fullerton-463561

SSC-Addicted

Points: 437

January 27, 2009 at 10:27 am

#933359

This works Thanks Cris

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

David Fullerton (1/27/2009)
This works Thanks Cris

Thanks for the feedback David.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden