Remove range of entries

[domenu]

Hey,

a little SQL question:

I have a SQL-database which contains barcodes(strings).

Now I have to delete a range of barcodes from the DB,
I know where the deletion begins (startbarcode to delete and the stopbarcode).

I illustrate my question with a simple example:

my database contains the following barcodes

400123000001
400123000002
400123000003
400123000004
400123000005

Now I have to delete all barcodes from barcode '400123000002' to barcode '400123000004'.

The resulting database must be:

400123000001
400123000005


What is the most efficient way to delete this range of barcodes from the SQL-database?

Thanks in advance

nick

 

[skuhlman]

Assuming all strings in that field are the same length, use can use :

DELETE MyTable WHERE MyBarCode >= '400123000002' and MyBarCode <= '400123000004'

 

[Rhys666]

Depends on the datatype of the field really. As per the above, if it's a string type field you can use the BETWEEN clause (which is inclusive) and CAST the string type data to a numeric data type for the comparison;

DELETE MyTable WHERE CAST(MyBarCode AS LONG) BETWEEN 400123000002 AND 400123000004

or of course it may already be a numeric data type;

DELETE MyTable WHERE MyBarCode BETWEEN 400123000002 AND 400123000004

Rhys
Buffy: Spike, what are you doing here, five words or less!
Spike: Out for a walk... bitch!

 

[domenu]

Thanks for the quick response!

One more question:

How to add a range of barcode from an existing database
to the resulting database with a single SQL-query?

I tried this:
cmd.CommandText = "INSERT INTO checklist (barcode) WHERE CAST(barcode AS LONG) BETWEEN " + iFromBarcode + " AND " + iToBarcode;

But this doesn't seem to work...

Experienced SQL-programmers:Help me out (again), please!!

Thank you very much,

nick

 

[ArtieChoke]

You need a source for the insert:

INSERT INTO checklist (barcode)
(select barcode from <other table> where ...)

 

[domenu]

Thanks for the help!