Delete Query

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

"trawets" <stewartrsmith@gmail.com> wrote in message
news:cf034d87-f6f8-4f63-931d-d1d016406d0f@d21g2000prf.googlegroups.com...
>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

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.

On Mar 26, 8:35*pm, "Richard" <root@localhost> wrote:
> "trawets" <stewartrsm...@gmail.com> wrote in message
>
> news:cf034d87-f6f8-4f63-931d-d1d016406d0f@d21g2000prf.googlegroups.com...
>
>
>
>
>
> >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
>
> 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.- Hide quoted text -
>
> - Show quoted text -

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

On Wed, 26 Mar 2008 13:46:22 -0700 (PDT), trawets wrote:
> On Mar 26, 8:35*pm, "Richard" <root@localhost> wrote:
>> "trawets" <stewartrsm...@gmail.com> wrote in message
>>
>> news:cf034d87-f6f8-4f63-931d-d1d016406d0f@d21g2000prf.googlegroups.com...
>>
>>
>>
>>
>>
>> >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
>>
>> 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.- Hide quoted text -
>>
>> - Show quoted text -
>
> 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

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.

> SQL query:
> SELECT *
> FROM jos_content
> WHERE state = -1
> AND DATE_ADD( publish_down, INTERVAL '90'
> DAY ) = CURRENT_DATE
> LIMIT 0 , 30
>
> 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

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.terantula.com/
Projects and administration - +31 6 5140 5160

On Mar 26, 9:08*pm, "Peter H. Coffin" <hell...@ninehells.com> wrote:
> On Wed, 26 Mar 2008 13:46:22 -0700 (PDT), trawets wrote:
> > On Mar 26, 8:35*pm, "Richard" <root@localhost> wrote:
> >> "trawets" <stewartrsm...@gmail.com> wrote in message
>
> >>news:cf034d87-f6f8-4f63-931d-d1d016406d0f@d21g2000prf.googlegroups.com....
>
> >> >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
>
> >> 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.- Hide quoted text -
>
> >> - Show quoted text -
>
> > 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
>
> 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.- Hide quoted text -
>
> - Show quoted text -

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

On Mar 26, 9:43*pm, trawets <stewartrsm...@gmail.com> wrote:
> On Mar 26, 9:08*pm, "Peter H. Coffin" <hell...@ninehells.com> wrote:
>
>
>
>
>
> > On Wed, 26 Mar 2008 13:46:22 -0700 (PDT), trawets wrote:
> > > On Mar 26, 8:35*pm, "Richard" <root@localhost> wrote:
> > >> "trawets" <stewartrsm...@gmail.com> wrote in message
>
> > >>news:cf034d87-f6f8-4f63-931d-d1d016406d0f@d21g2000prf.googlegroups.com....
>
> > >> >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
>
> > >> 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.- Hide quoted text -
>
> > >> - Show quoted text -
>
> > > 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
>
> > 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.- Hide quoted text -
>
> > - Show quoted text -
>
> 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- Hide quoted text -
>
> - Show quoted text -

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