Delete Query

Delete Query

Post by trawet » Fri, 28 Mar 2008 05:31:45


I am trying to run the MySQL delete query in phpMyAdmin, now the query
runs and doesn't give any syntax errors, but it also doesn't delete
and rows

From the query you can see the table name (jos_content) and two
fields, state (-1 = unpublished) and publish_down,

DELETE FROM jos_content WHERE state=-1 AND DATE_ADD(publish_down,
INTERVAL '90' DAY) = CURRENT_DATE

so what I am trying to achieve is that any entries that are not
published and and its been 90 days since they were unpublished I would
like to delete them, but as I say it runs but does not delete any
entries so I'm guessing that there is something wrong with interval
part
any suggestions please
 
 
 

Delete Query

Post by Richar » Fri, 28 Mar 2008 05:35:00


What do you get as results when you do the same selection in a select
statement?

SELECT * FROM jos_content WHERE state=-1 AND DATE_ADD(publish_down,
INTERVAL '90' DAY) = CURRENT_DATE

'90' ? Sure a text value? Not a numeric value there?

R.

 
 
 

Delete Query

Post by trawet » Fri, 28 Mar 2008 05:46:22


Hi
Thanks for replying

resuts of query
MySQL returned an empty result set (i.e. zero rows).

SQL query:
SELECT *
FROM jos_content
WHERE state = -1
AND DATE_ADD( publish_down, INTERVAL '90'
DAY ) = CURRENT_DATE
LIMIT 0 , 30

Im not sure what you mean by this
" '90' ? Sure a text value? Not a numeric value there?"

when I run the query for state only I get
21,705 rows where most of these have been unpulished for 90 days or
more
 
 
 

Delete Query

Post by Peter H. C » Fri, 28 Mar 2008 06:08:41


You are not asking for "or more" in your query. You are asking for
*exactly* 90 days. And be careful when changing that equals sign. It'll
be backawards from what you may want to use at first.

--
Cunningham's First Law:
Any sufficiently complex deterministic system will exhibit
non-deterministic behaviour.
 
 
 

Delete Query

Post by Mark Huize » Fri, 28 Mar 2008 06:17:14

> SQL query:

I think 'or more' is the importnt thing here. You look for rows
_exactly_ 90 days ago.

mark

--
Terantula - Industrial Strength Open Source - http://www.yqcomputer.com/
Projects and administration - +31 6 5140 5160
 
 
 

Delete Query

Post by trawet » Fri, 28 Mar 2008 06:43:17


I get what you mean now
when I add the <> operators to the select query I get records,
some 8000 for > and 15000 for < ,
so your very right I need to be careful,
this is a bit spooky
 
 
 

Delete Query

Post by trawet » Fri, 28 Mar 2008 07:04:12

n Mar 26, 9:43m, trawets < XXXX@XXXXX.COM > wrote:

Hi Thanks to you all for helping out on this

this quer seems to work just fine

DELETE FROM jos_content WHERE state=-1 AND DATE_ADD(publish_down,
INTERVAL '90' DAY) <= CURRENT_DATE

I had to ensure it was the correct entries I wanted
Thanks again