Member Avatar for Barefootsanders

Hey everyone,

I'm attempting to delete records within my database but I'm having problems writing the query. What I need to do is delete records from my database that, within a field in this record, have a substring that I will pass to the mysql command. So for example,

A table has one field called filePath.
Example records:
C:\New Folder\test.txt
C:\New Folder\test1.txt
C:\New Folder\test1.txt
C:\Other Folder\test.txt
C:\Other Folder\test1.txt
C:\Other Folder\test2.txt

I would like to send a command that will delete records within the table where a substring of the filePath field matches 'C:\New Folder'. In this case, the top 3 records would be deleted.

Anyone know the proper syntax for a statement like this?

Thanks in advance.
-Barefoot

  • 2 Contributors
  • 3 Replies
  • 70 Views
  • 16 Hours Discussion Span
  • Latest Post Latest Post by trudge
Member Avatar for trudge

And what have you tried so far that doesn't work?

Member Avatar for Barefootsanders

Ive read through mysql 5.0 documentation, tried using REGEX, LIKE, and strcmp(s1,s2) and nothing seems to work. I was wondering if maybe I missed something in the documentation.

Thanks.

Member Avatar for trudge

Um, have you tried looking up the DELETE command? You will also want to qualify it with a WHERE clause, otherwise disaster is sure to follow. Try doing a SELECT ... WHERE first to make sure you are getting the right result set back, then swap the SELECT statement for a DELETE statement.

DELETE FROM your_table 
WHERE filePath LIKE 'C:\New Folder\%'

The '%' at the end of the search string is a wild card matching anything at the end.

You would do well to get yourself at least one good book on SQL in general, or MySQL if you can. Reading reference documentation for programs is like trying to learn how to speak a new language by reading a dictionary :) Look for 'MySQL' by Paul DuBois or for a general RDBMS book try 'Database Design for Mere Mortals' by Michael J. Hernandez.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.