Access SQL Query to Delete records based on Condition

I have a table that needs to be dedicated to just one customer. However, the records do not have the customer names listed like I would like. I need to delete some records based on a condition that is one of the fields.

Table Name: DSCNSKPIUPDATE
Field Name: Reject

Condition: R0*

So In my field "Reject" I have several different formats of text. However, I only want to keep the records that start with "R0".

** The records have back up copies for the other customers **

The text look like these...
R0117730
R0116926
R0118216
or
N/A
20140903436
2014070323
1
To be released

As you can see there are many different formats. I need to remove all records that do not start with "R0".
ggodwinAsked:
Who is Participating?
 
Saurabh Singh TeotiaCommented:
You can use this query..

I'm assuming you are using this in access:-

Delete * from  DSCNSKPIUPDATE
where left(Reject,2)<>"RO"

Open in new window


Change the RO one in single quotes if you are working in SQL and remove * from the same...

Saurabh...
0
 
ggodwinAuthor Commented:
Worked perfectly
0
 
ggodwinAuthor Commented:
If the field "Reject" is NULL can I use this?

Delete * from DSCNSKPIUPDATE
Where Reject is NULL

Open in new window

0
 
Saurabh Singh TeotiaCommented:
Yeah that should be taken care of as the left,2 is not RO but you can use this...

Delete * from  DSCNSKPIUPDATE
where left(Reject,2)<>"RO"  or reject is null

Open in new window


This will do that...

Saurabh...
0
 
ggodwinAuthor Commented:
Thank you. For what ever reason it did not get covered in the Left,2
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

Advertise Here