Delete multiple records

[mr moe]

how do i delete multiple records in a table, for example if i have a table that as serial_num 123 twice, how do i delete the two records, basically i don't want to delete one record, i want to delete the two records. Thanks.

 

[tehNellie]

DELETE FROM tblname WHERE [serial_num] = 123;

in SQL view would delete all instances in the table where the serial number is 123

 

[Alc]

I think something along the following lines should do it

DELETE * FROM [I]tablename[/I] 
WHERE Serial_Num IN
(SELECT Serial_Num
FROM [I]tablename[/I]
GROUP BY Serial_Num
HAVING Count(Serial_Num) > 1;);

 

[mr moe]

here is a copy

Here is a copy of my database, basically, I came up with another idea but i can't fiqure out how to do it,
in the sample i have a how_to_do_it_qry, what i want to do is exclude any serial_num that has more than one record, the idea is to have " Having count(serial_num)<2;
but it's not working could you please guys try it.


again i want to exclude all serial_num that have count = 2 or more, thanks.

 

[Alc]

As I suggested, this does it

DELETE * FROM change_order_tbl
WHERE serial_num IN
(SELECT change_order_tbl.serial_num
FROM change_order_tbl
GROUP BY change_order_tbl.serial_num
HAVING Count(change_order_tbl.serial_num)>1;);

 

[mr moe]

As I suggested, this does it

DELETE * FROM change_order_tbl
WHERE serial_num IN
(SELECT change_order_tbl.serial_num
FROM change_order_tbl
GROUP BY change_order_tbl.serial_num
HAVING Count(change_order_tbl.serial_num)>1;);

thanks. it worked perfect.