Delete query help

Question

Post reply

Delete query help

David Fullerton-463561

SSC-Addicted

Points: 417
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

Abhijit More

SSCertifiable

Points: 6897

January 27, 2009 at 7:53 am

#933199

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: 417 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: 417 More actions · Answer 4

David Fullerton-463561

SSC-Addicted

Points: 417

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