Removing records

Hi, i'm not so good in Sql so can somebody help me.
Let say i have 10 records. Each records have a id
1, bmw
2, peugeot
3, ford
5, citrone
6, mazda
7, volvo
8, renault
9, chrysler
10, alfa
Now i receive a txt with the id who are still valid (in stock) and have to
remove the cars sold.
Lets say: 2,3,4,6,7,9,10 are still in stock, 1,5 and 8 are sold.
Of course i can use Recordset and check but i wonder if i can do that i one
sql statement ?
GL.DELETE FROM tablename WHERE ID NOT IN (2,3,4,6,7,9,10)
HTH. Ryan
"Grard Leclercq" <gerard.leclercq@.pas-de-mail.fr> wrote in message
news:mEOyf.118557$mB2.6107192@.phobos.telenet-ops.be...
> Hi, i'm not so good in Sql so can somebody help me.
> Let say i have 10 records. Each records have a id
> 1, bmw
> 2, peugeot
> 3, ford
> 5, citrone
> 6, mazda
> 7, volvo
> 8, renault
> 9, chrysler
> 10, alfa
> Now i receive a txt with the id who are still valid (in stock) and have to
> remove the cars sold.
> Lets say: 2,3,4,6,7,9,10 are still in stock, 1,5 and 8 are sold.
> Of course i can use Recordset and check but i wonder if i can do that i
> one sql statement ?
> GL.
>
>
>
>|||Hi Gerard,
BEGIN TRAN -- Just in case!
DELETE yourtable
WHERE id NOT IN ( SELECT id FROM yourtxtfile_as_a_table )
Now check...
SELECT *
FROM yourtable
If it worked...
COMMIT TRAN
otherwise...
ROLLBACK
Note, this relies on the id's relating to the same entity between
databases - can you gaurentee that?
You can use DTS to load the textfile into SQL Server, or if you just want to
delete by id then...
DELETE yourtable WHERE id IN ( ones to remove seperated by commas )
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Grard Leclercq" <gerard.leclercq@.pas-de-mail.fr> wrote in message
news:mEOyf.118557$mB2.6107192@.phobos.telenet-ops.be...
> Hi, i'm not so good in Sql so can somebody help me.
> Let say i have 10 records. Each records have a id
> 1, bmw
> 2, peugeot
> 3, ford
> 5, citrone
> 6, mazda
> 7, volvo
> 8, renault
> 9, chrysler
> 10, alfa
> Now i receive a txt with the id who are still valid (in stock) and have to
> remove the cars sold.
> Lets say: 2,3,4,6,7,9,10 are still in stock, 1,5 and 8 are sold.
> Of course i can use Recordset and check but i wonder if i can do that i
> one sql statement ?
> GL.
>
>
>
>|||Are you saying that you want to delete records for cars with ids 1, 5, and
8?
A simple in clause will take care of this...
Delete from MyTable
where CarID in (1,5,8)
However, if your application is going to be doing this on a regular basis,
with a different list each time, you are probably better off doing the
database updates one at a time. The performance will not be quite as good,
but the code will be much simpler to maintain. With the example that you
give here, the performance difference would be negligible unless you are
performing thousands and thousands of updates.
Note, if what I posted is what you are looking for, then you probably want
to find a good beginner's SQL site and pick up some tips there (I would
reccomend one, but I'm afraid I don't know any off hand.). It will prove
much more useful than any newsgroup, at least until you get to some more
complicated problems.
Best of luck.
"Grard Leclercq" <gerard.leclercq@.pas-de-mail.fr> wrote in message
news:mEOyf.118557$mB2.6107192@.phobos.telenet-ops.be...
> Hi, i'm not so good in Sql so can somebody help me.
> Let say i have 10 records. Each records have a id
> 1, bmw
> 2, peugeot
> 3, ford
> 5, citrone
> 6, mazda
> 7, volvo
> 8, renault
> 9, chrysler
> 10, alfa
> Now i receive a txt with the id who are still valid (in stock) and have to
> remove the cars sold.
> Lets say: 2,3,4,6,7,9,10 are still in stock, 1,5 and 8 are sold.
> Of course i can use Recordset and check but i wonder if i can do that i
one
> sql statement ?
> GL.
>
>
>
>|||Thank you all, i didn't know it was so easy to do. Time to learn sql
profoundly. Thanks. GL|||You might also want to look up the codes used for automobile companies
and product lines. They are alphabetic and you can quickly learn them
by sight.
ALWAYS research your customer's industry first.