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!
6  1812 
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!
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!
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!
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!
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'"
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!
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: r |
last post by:
I am trying to do a delete query like this one:
DELETE FROM pending WHERE pages.url=pending.url AND
pages.date=pending.date;
If I run it (via PHP) I get the following error: Unknown table...
|
by: Joe Del Medico |
last post by:
I have two tables A & B and I want to delete all the records in A that
are not in B. Can I do this in the query builder? It seems like a
simple problem.
I can easily find the records in A that...
|
by: Mark Reed |
last post by:
Hi All,
I have a table with a date field and 6 number fields. The number fields are
not in numerical order across the fields and I want number 1 to be the
lowest number and number 6 to be the...
|
by: Mark Reed |
last post by:
Hi all,
Please help. I have a table with 2 fields of which I am trying to change
a select query into a delete query.
the select query is:
SELECT Table1.Date, Min(Table1.Ball) AS MinOfBall...
|
by: Dalan |
last post by:
Having searched the archives regarding a Delete Query, I found nothing
specific to my need, although there seems to be a plethora of ideas
and suggestions on queries in general. I have used Delete...
|
by: Darin |
last post by:
I have a table that I want to delete specific records from based on
data in other tables. I'm more familiar with Access '97, but am now
using 2003, but the database is in 2000 format. In '97, I...
|
by: forbes |
last post by:
Hi,
I have a user that used the Query Wizard to create a query in Access.
Now she claims that her master table is missing all the data that was
excluded from the query. Can you create anything...
|
by: JC |
last post by:
Hello,
I am trying to change a select, find duplicates, query into a delete
query. I want to get rid of the records in the main table
"tblHurnsHistory." I changed the Find Duplicates query to...
|
by: Kevin M |
last post by:
I have one table and have created a form from that table. Also, I have
created a delete query. I want to but a button on the form which will delete
all records from the table; however, I cannot get...
|
by: austin1539 |
last post by:
-Access 2002
-Windows XP Pro
Trying to run a DELETE Query to delete each entry in the table 'Data' where the field 'AssocID' matches the field 'AssocID' in the table 'Queries' and the date is...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |
