Delete using another table's values

[SG]

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.v ins;

But cannot replicate this in SQL server?

Any help would be much appreciated.

Thanks
Sam

[Hugo Kornelis]

Re: Delete using another table's values

On 23 Sep 2004 02:40:24 -0700, SG wrote:
[color=blue]
> 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?[/color]

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.v ins

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.v ins)

(both queries untested - beware of spelling errors!)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Sam jones]

Re: Delete using another table's values


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!

[Andrey]

Re: Delete using another table's values

Hugo Kornelis wrote:
[color=blue]
> On 23 Sep 2004 02:40:24 -0700, SG wrote:
>
>[color=green]
>> I access the query would be:
>>DELETE warranty.*[/color]
>[color=green]
>>FROM warranty INNER JOIN registrations ON warranty.BBMQCE =[/color]
>[color=green]
>>registrations .vins;
>>
>>But cannot replicate this in SQL server?[/color]
>
>
> 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.v ins
>
> 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.v ins)
>
> (both queries untested - beware of spelling errors!)
>
> Best, Hugo[/color]

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

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

[Hugo Kornelis]

Re: Delete using another table's values

On Sat, 25 Sep 2004 06:01:13 GMT, Andrey wrote:
[color=blue]
>There shouldn't be *NOT* in WHERE clause, because SG wants to delete all matches[/color]

Hi Andrey,

Good catch! Thanks for correcting my mistake.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Andrey]

Re: Delete using another table's values

Hugo Kornelis wrote:[color=blue]
> On Sat, 25 Sep 2004 06:01:13 GMT, Andrey wrote:
>
>[color=green]
>>There shouldn't be *NOT* in WHERE clause, because SG wants to delete all matches[/color]
>
>
> Hi Andrey,
>
> Good catch! Thanks for correcting my mistake.
>
> Best, Hugo[/color]

You're welcome :)