Delete using an inner join??? 2

[NewbiDoobie]

I am trying to covert the next section of code to be performed using an inner join opposed to using the covert process.

(I think I am going to have to do this as 2 seperate commands, but this statements does not seem to be working

delete	a

from	@NotInXref a
	left outer join 
	@CurrentXref cx on (a.year -1) = cx.year
			and a.make = cx.make 
			and a.model = cx.model
	where a.vehicleid is null

Here is what the original statement looked like:

 delete	

from	@NotInXref

where convert(varchar(20), [Year]) + '-' + Make + '-' + Model 

	not in (
		select	convert(varchar(20), [Year]) + '-' + Make + '-' + Model
		from	CurrentXref cx
	)
	OR convert(varchar(20), [Year] - 1) + '-' + Make + '-' + Model 
	not in (
		select	convert(varchar(20), [Year]) + '-' + Make + '-' + Model
		from	CurrentXref cx
	)

 

[hmckillop]

Cant do it with an inner join, left join is the best I can manage as you use not in.

delete tmp
from @notinXref tmp
LEFT JOIN (select    convert(varchar(20), [Year]) + '-' + Make + '-' + Model as field1
        from    CurrentXref cx) tmp2 on tmp2.field1 = convert(varchar(20), [Year]) + '-' + Make + '-' + Model  or tmp2.field2 = convert(varchar(20), [Year]-1) + '-' + Make + '-' + Model 
WHERE tmp2.WhatEverFieldIsIdOnCurrentXrefTable is null

Though I dont think the whole conversion to varchar et al will be very performant!

"I'm living so far beyond my income that we may almost be said to be living apart

 

[NewbiDoobie]

The field lengths are the same in both tables, the original programmer did not know how to do the join, so he placed it in as a single field to make it a single field to compare

That is why I pressed to get out of the conversions first.

 

[gmmastros]

You have 2 table variables @NotInXRef and @CurrentXRef. You want to delete records from @NotInXRef where there is no corresponding record in @CurrentXRef. If I understand this correctly, then your attempt is 'pretty darn close'.

delete    a

from    @NotInXref a
    left outer join 
    @CurrentXref cx on (a.year -1) = cx.year
            and a.make = cx.make 
            and a.model = cx.model
    where [red]cx.<some field> is null[/red]

There wouldn't be any records from the @NotInXRef that are null (because of the join) because you are left joining the other table.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[hmckillop]

gmmastros - if you look at the original query there was an or statement, for the current year or the previous year.
so I supose its just an additional line

delete    a

from    @NotInXref a
    left outer join 
    @CurrentXref cx on ((a.year -1) = cx.year or (a.year) = cx.year)
            and a.make = cx.make 
            and a.model = cx.model
    where cx.<some field> is null

"I'm living so far beyond my income that we may almost be said to be living apart

 

[gmmastros]

Good catch. I missed it. As always, without data it's hard to test to make sure the results are right.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[NewbiDoobie]

Thanks guys