By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,571 Members | 3,168 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,571 IT Pros & Developers. It's quick & easy.

Delete query?

P: n/a
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!

Jul 14 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a
On 14 Jul 2006 05:57:50 -0700, "dan" <it*******@gmail.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!
Jul 14 '06 #2

P: n/a
Shorter:

DELETE * FROM My_table WHERE ColB = "Spill"

HBInc.
Tom van Stiphout wrote:
On 14 Jul 2006 05:57:50 -0700, "dan" <it*******@gmail.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!
Jul 15 '06 #3

P: n/a
Shorter:

DELETE * FROM My_table WHERE ColB = "Spill"

HBInc.
Tom van Stiphout wrote:
On 14 Jul 2006 05:57:50 -0700, "dan" <it*******@gmail.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!
Jul 15 '06 #4

P: n/a
On 15 Jul 2006 14:46:24 -0700, "hbinc" <j.********@hccnet.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" <it*******@gmail.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!
Jul 15 '06 #5

P: n/a
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'"

Jul 15 '06 #6

P: n/a
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.openrecordset("SELECT * FROM My_table WHERE
ColB = 'Spill'")
do while (not cur_set.eof)
currentdb.execute "DELETE * FORM My_table WHERE ColA = " &
cur_set!ColA
cur_set.movenext
loop
HBInc.

Tom van Stiphout wrote:
On 15 Jul 2006 14:46:24 -0700, "hbinc" <j.********@hccnet.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" <it*******@gmail.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!
Jul 18 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.