Delete using another tables values

Hi all,
I am trying to perform a delete that I could achieve in Access but
need to do this in sql2000.
I have two tables Warranty and Registrations. I would like to delete
all items in the warranty table where there is a match in
registrations on a common field.
I access the query would be:
DELETE warranty.*
FROM warranty INNER JOIN registrations ON warranty.BBMQCE =
registrations.vins;

But cannot replicate this in SQL server?

Any help would be much appreciated.

Thanks
SamOn 23 Sep 2004 02:40:24 -0700, SG wrote:

>I access the query would be:
>DELETE warranty.*
>FROM warranty INNER JOIN registrations ON warranty.BBMQCE =
>registrations.vins;
>But cannot replicate this in SQL server?

Hi Sam,

You're almost there. The Transact-SQL version of this would be

DELETE warranty
FROM warranty
INNER JOIN registration
ON warranty.BBMQCE = registrations.vins

Yes - you only need to drop the .* !!!

However, the above is proprietary code that will not port well to other
databases. If you want portability, use the ANSI-standard delete syntax
instead:

DELETE FROM warranty
WHERE NOT EXISTS (SELECT *
FROM registration
WHERE warranty.BBMQCE = registrations.vins)

(both queries untested - beware of spelling errors!)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Many thanks - i was so nearly there!
Sam

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Hugo Kornelis wrote:

> On 23 Sep 2004 02:40:24 -0700, SG wrote:
>
>>I access the query would be:
>>DELETE warranty.*
>>FROM warranty INNER JOIN registrations ON warranty.BBMQCE =
>>registrations.vins;
>>
>>But cannot replicate this in SQL server?
>
> Hi Sam,
> You're almost there. The Transact-SQL version of this would be
> DELETE warranty
> FROM warranty
> INNER JOIN registration
> ON warranty.BBMQCE = registrations.vins
> Yes - you only need to drop the .* !!!
>
> However, the above is proprietary code that will not port well to other
> databases. If you want portability, use the ANSI-standard delete syntax
> instead:
> DELETE FROM warranty
> WHERE NOT EXISTS (SELECT *
> FROM registration
> WHERE warranty.BBMQCE = registrations.vins)
> (both queries untested - beware of spelling errors!)
> Best, Hugo

DELETE FROM warranty
WHERE EXISTS (SELECT *
FROM registration
WHERE warranty.BBMQCE = registrations.vins)

There shouldn't be *NOT* in WHERE clause, because SG wants to delete all matches|||On Sat, 25 Sep 2004 06:01:13 GMT, Andrey wrote:

>There shouldn't be *NOT* in WHERE clause, because SG wants to delete all matches

Hi Andrey,

Good catch! Thanks for correcting my mistake.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis wrote:
> On Sat, 25 Sep 2004 06:01:13 GMT, Andrey wrote:
>
>>There shouldn't be *NOT* in WHERE clause, because SG wants to delete all matches
>
> Hi Andrey,
> Good catch! Thanks for correcting my mistake.
> Best, Hugo

You're welcome :)