Removing rows from table

[xsiteu]

Any SQL people out there can proably answer this in a few seconds.

I want to remove rows from a table that are older than a certain date.

I am OK building all sorts of select statements but not one for a date range. Part of the problem is probably that this is just a char field and not a date field so cannot to date arithmetic on it.

In fact I would be happy just to delete all record containing "2003"

I have tried the following:

SELECT * FROM `tblTgp` WHERE `date` LIKE '2003%'

will return the rows I would like to delete but

DELETE * FROM `tblTgp` WHERE `date` LIKE '2003%'

results in an error

Here is the table structure but the only item of interest is probably just the date field:

id int(11) No auto_increment
nickname char(30) No
email char(100) No
url char(150) No
category char(100) No
description char(100) No
date char(8) No
newpost char(10) No yes
accept char(10) No
vote int(3) No 5
recip char(5) No
sessionid char(45) No

Here is a typical data field entry

20031224

Using My PHP (Unix)

[Entreri]

Originally posted by xsiteu
I have tried the following:

SELECT * FROM `tblTgp` WHERE `date` LIKE '2003%'

will return the rows I would like to delete but

DELETE * FROM `tblTgp` WHERE `date` LIKE '2003%'

DELETE tblTgp WHERE date LIKE '2003%'

Entreri.

[xsiteu]

Entreri,

Thanks for the suggestion but no luck

Result:

Error

SQL-query :

DELETE tblTgp WHERE date LIKE '2003%'

MySQL said:


#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE date LIKE '2003%'' at line 1

[Documentation] · [Back]

[Entreri]

Originally posted by xsiteu
Entreri,

Thanks for the suggestion but no luck

Result:

Okay, perhaps mysql is stricter on the query. (I usually use ms-sql)

Try this :

DELETE FROM tblTgp WHERE date LIKE '2003%'

Alas, I'm leaving to see WrestleMania XX at a movie theater shortly so I cannot help further right now.

Entreri.

ps. See mysql doc if it still doesn't work. http://www.mysql.com/doc/en/DELETE.html

[xsiteu]

That did the trick.

Thanks heaps and I hope you enjoy WrestleMania XX

Ralph.