Delete query?

[dan]

Hi,

I have a table like this:

ColA ColB
22 Drink
22 Eat
22 Spill
23 Drink
23 Eat
24 Drink
24 Spill


I want to delete all records for a number where ColB = Spill. For
example, since 22 has a Spill Entry and 24 has a spill entry, it would
delete all rows where ColA has 22 and where ColA has 24. I'm guessnig a
SQL query would do this?

Any help is very appreciated!! Thanks!

[Tom van Stiphout]

Re: Delete query?

On 14 Jul 2006 05:57:50 -0700, "dan" <itzdan262@gmai l.comwrote:

delete * from MyTable
where ColA in (select ColA from MyTable where ColB='Spill')

-Tom.

>Hi,
>
>I have a table like this:
>
>ColA ColB
>22 Drink
>22 Eat
>22 Spill
>23 Drink
>23 Eat
>24 Drink
>24 Spill
>
>
>I want to delete all records for a number where ColB = Spill. For
>example, since 22 has a Spill Entry and 24 has a spill entry, it would
>delete all rows where ColA has 22 and where ColA has 24. I'm guessnig a
>SQL query would do this?
>
>Any help is very appreciated!! Thanks!

[hbinc]

Re: Delete query?

Shorter:

DELETE * FROM My_table WHERE ColB = "Spill"

HBInc.


Tom van Stiphout wrote:

On 14 Jul 2006 05:57:50 -0700, "dan" <itzdan262@gmai l.comwrote:
>
delete * from MyTable
where ColA in (select ColA from MyTable where ColB='Spill')
>
-Tom.
>
>

Hi,

I have a table like this:

ColA ColB
22 Drink
22 Eat
22 Spill
23 Drink
23 Eat
24 Drink
24 Spill


I want to delete all records for a number where ColB = Spill. For
example, since 22 has a Spill Entry and 24 has a spill entry, it would
delete all rows where ColA has 22 and where ColA has 24. I'm guessnig a
SQL query would do this?

Any help is very appreciated!! Thanks!

[hbinc]

Re: Delete query?

Shorter:

DELETE * FROM My_table WHERE ColB = "Spill"

HBInc.


Tom van Stiphout wrote:

On 14 Jul 2006 05:57:50 -0700, "dan" <itzdan262@gmai l.comwrote:
>
delete * from MyTable
where ColA in (select ColA from MyTable where ColB='Spill')
>
-Tom.
>
>

Hi,

I have a table like this:

ColA ColB
22 Drink
22 Eat
22 Spill
23 Drink
23 Eat
24 Drink
24 Spill


I want to delete all records for a number where ColB = Spill. For
example, since 22 has a Spill Entry and 24 has a spill entry, it would
delete all rows where ColA has 22 and where ColA has 24. I'm guessnig a
SQL query would do this?

Any help is very appreciated!! Thanks!

[Tom van Stiphout]

Re: Delete query?

On 15 Jul 2006 14:46:24 -0700, "hbinc" <j.van.gils@hcc net.nlwrote:

That's not the same as what I was reading into the original message.
It seemed to indicate that he wanted to delete ALL rows with ColA=22
and ALL rows with ColA=24, because they had ONE row with Spill.

Your statement only deletes (22,Spill) and (24,Spill)

-Tom.

>Shorter:
>
>DELETE * FROM My_table WHERE ColB = "Spill"
>
>HBInc.
>
>
>Tom van Stiphout wrote:

>On 14 Jul 2006 05:57:50 -0700, "dan" <itzdan262@gmai l.comwrote:
>>
>delete * from MyTable
>where ColA in (select ColA from MyTable where ColB='Spill')
>>
>-Tom.
>>
>>

>Hi,
>
>I have a table like this:
>
>ColA ColB
>22 Drink
>22 Eat
>22 Spill
>23 Drink
>23 Eat
>24 Drink
>24 Spill
>
>
>I want to delete all records for a number where ColB = Spill. For
>example, since 22 has a Spill Entry and 24 has a spill entry, it would
>delete all rows where ColA has 22 and where ColA has 24. I'm guessnig a
>SQL query would do this?
>
>Any help is very appreciated!! Thanks!

[Lyle Fairfield]

Re: Delete query?

hbinc wrote:

DELETE * FROM My_table WHERE ColB = "Spill"

I think this will delete only two records while OP wants to delete 5.

Tom's IN operator should work swimmingly. In case anyone else tries to
avoid INs as I do [for no particularly good reason], I offer a JOIN.

DBEngine(0)(0). Execute _
"DELETE t21.* FROM Table2 t21 " _
& "INNER JOIN Table2 t22 " _
& "ON t21.ColA = t22.ColA " _
& "WHERE t22.ColB='spill '"

[hbinc]

Re: Delete query?

Hi Tom,

You are right. I was more focussed on the previous remark than on the
original question.
Normally I would solve this as follows:

dim cur_set as recordset

set cur_set = currentdb.openr ecordset("SELEC T * FROM My_table WHERE
ColB = 'Spill'")
do while (not cur_set.eof)
currentdb.execu te "DELETE * FORM My_table WHERE ColA = " &
cur_set!ColA
cur_set.movenex t
loop


HBInc.

Tom van Stiphout wrote:

On 15 Jul 2006 14:46:24 -0700, "hbinc" <j.van.gils@hcc net.nlwrote:
>
That's not the same as what I was reading into the original message.
It seemed to indicate that he wanted to delete ALL rows with ColA=22
and ALL rows with ColA=24, because they had ONE row with Spill.
>
Your statement only deletes (22,Spill) and (24,Spill)
>
-Tom.
>
>

Shorter:

DELETE * FROM My_table WHERE ColB = "Spill"

HBInc.


Tom van Stiphout wrote:

On 14 Jul 2006 05:57:50 -0700, "dan" <itzdan262@gmai l.comwrote:
>
delete * from MyTable
where ColA in (select ColA from MyTable where ColB='Spill')
>
-Tom.
>
>
Hi,

I have a table like this:

ColA ColB
22 Drink
22 Eat
22 Spill
23 Drink
23 Eat
24 Drink
24 Spill


I want to delete all records for a number where ColB = Spill. For
example, since 22 has a Spill Entry and 24 has a spill entry, it would
delete all rows where ColA has 22 and where ColA has 24. I'm guessnig a
SQL query would do this?

Any help is very appreciated!! Thanks!