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.vins;

But cannot replicate this in SQL server?

Any help would be much appreciated.

Thanks
Sam

[Hugo Kornelis]

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
--

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

[Sam jones]

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]

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

[Hugo Kornelis]

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)

[Andrey]

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 :)