asked on

delete records not working correctly in transact sql

I have a table with a column named filename. I am trying to delete all of my records that start with a "9-" except not delete records that start with "9-5400". This statement doesn't work:

delete from tblmaster where substring(filename,1,2) = '9-' and substring(filename,1,6) != '9-5400'

Can someone help me rephrase this string so it ignores the 9-5400 but deletes everything else that starts with 9-.

Thanks!

ASKER CERTIFIED SOLUTION

Anuj

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lcohan

Try this then replace select with delete

select * from tblmaster
where substring(ltrim(filename),1,2) = '9-' and substring(ltrim(filename),1,6) != '9-5400'

jogos

A little modification on previous comment (credits!),
Always include the 'modifications' you do in the select list. And if you don't get what you want, one by one comment out the filter until you see where the problem is.
As Icohan already suggested there could be a space before the filename

select substring(filename,1,2),substring(filename,1,6), *
from tblmaster
where substring(ltrim(filename),1,2) = '9-'
and substring(ltrim(filename),1,6) != '9-5400'

And before trying a delete .... always try it as a select first so that you can verify it is realy what you want.

jogos

But I would write it as a LIKE, but it won't change the outcome.

where filename like '9-%'
and filename not like '9-5400%'

Bob Butcher

ASKER

Thank you!