help with query (delete lowest value)

i have a table with objects in categories and their positions.

there will be several rows with category 400, and they will have
various positions, i want to delete only the row with the lowest
position.

i can select the row i want to delete, but don't know how to delete
just this row.

here's my select:

SELECT * FROM categories WHERE category = 400 order by position limit 1

thanks for your help.
s7

ps - is there any cron-like facility within mysql to do this for me,
once every weekday?

Re: help with query (delete lowest value)

wrote in message
news:1142878117.608567.178220@z34g2000cwc.googlegroups.com.. .
> there will be several rows with category 400, and they will have
> various positions, i want to delete only the row with the lowest
> position.
>
> SELECT * FROM categories WHERE category = 400 order by position limit 1

Are you using MySQL 4.1 or later? If so, you can use subqueries.
Does this table have a primary key? I'll assume yes for both.

DELETE FROM categories WHERE primaryKey = (SELECT primaryKey FROM categories
WHERE category = 400 ORDER BY position LIMIT 1)

Alternatively, here's a solution that needs neither primary keys nor
subqueries. It uses an outer join to find the row for which there is no
other row with a lower position value.

DELETE c1
FROM categories AS c1 LEFT OUTER JOIN categories AS c2
ON c1.category = c2.category AND c1.position > c2.position
WHERE c1.category = 400 AND c2.position IS NULL

I have not tested this. You should try this on a copy or a mockup of your
database to satisfy yourself that it works correctly.

> ps - is there any cron-like facility within mysql to do this for me,
> once every weekday?

What operating system? UNIX/Linux actually do have cron of course, so I'll
assume you mean this is for Windows.

Windows XP has its Task Scheduler Service.
http://www.microsoft.com/technet/prodtechnol/windows2000serv /evaluate/featfunc/taskschd.mspx
However, in my experience, Task Scheduler is broken in Windows XP SP2; see
http://support.microsoft.com/default.aspx?scid=kb;en-us;8845 73

You can also get cron for Windows in Microsoft's free "Services for UNIX"
product.
http://www.microsoft.com/windowsserversystem/sfu/downloads/d efault.asp

You can also get cron for Windows in Cygwin.
http://www.cygwin.com/

Regards,
Bill K.

Re: help with query (delete lowest value)

Bill Karwin wrote:
> wrote in message
> news:1142878117.608567.178220@z34g2000cwc.googlegroups.com.. .
> > there will be several rows with category 400, and they will have
> > various positions, i want to delete only the row with the lowest
> > position.
> >
> > SELECT * FROM categories WHERE category = 400 order by position limit 1
>
> Are you using MySQL 4.1 or later? If so, you can use subqueries.
> Does this table have a primary key? I'll assume yes for both.
>
> DELETE FROM categories WHERE primaryKey = (SELECT primaryKey FROM categories
> WHERE category = 400 ORDER BY position LIMIT 1)

i am using 4.0.24 and received an error, i guess that's why... i will
try something like the below. thanks for your help bill.

s7
>
> Alternatively, here's a solution that needs neither primary keys nor
> subqueries. It uses an outer join to find the row for which there is no
> other row with a lower position value.
>
> DELETE c1
> FROM categories AS c1 LEFT OUTER JOIN categories AS c2
> ON c1.category = c2.category AND c1.position > c2.position
> WHERE c1.category = 400 AND c2.position IS NULL
>
> I have not tested this. You should try this on a copy or a mockup of your
> database to satisfy yourself that it works correctly.
>
> > ps - is there any cron-like facility within mysql to do this for me,
> > once every weekday?
>
> What operating system? UNIX/Linux actually do have cron of course, so I'll
> assume you mean this is for Windows.
>
> Windows XP has its Task Scheduler Service.
> http://www.microsoft.com/technet/prodtechnol/windows2000serv /evaluate/featfunc/taskschd.mspx
> However, in my experience, Task Scheduler is broken in Windows XP SP2; see
> http://support.microsoft.com/default.aspx?scid=kb;en-us;8845 73
>
> You can also get cron for Windows in Microsoft's free "Services for UNIX"
> product.
> http://www.microsoft.com/windowsserversystem/sfu/downloads/d efault.asp
>
> You can also get cron for Windows in Cygwin.
> http://www.cygwin.com/
>
> Regards,
> Bill K.

Re: help with query (delete lowest value)

tried the other query and received this:

#1066 - Not unique table/alias: 'c1'

shoud as c1 be as 'something else'?

Re: help with query (delete lowest value)

when i changed delete c1 to select * - the row is isolated, and it's
the one i want deleted...

Re: help with query (delete lowest value)

i meant when i changed 'delete from' to 'select * from'

Re: help with query (delete lowest value)

wrote in message
news:1142888719.232371.92970@v46g2000cwv.googlegroups.com...
> tried the other query and received this:
> #1066 - Not unique table/alias: 'c1'

Woops. This is probably due to a difference between MySQL 4.0 and 4.1.
Here's an excerpt from http://dev.mysql.com/doc/refman/4.1/en/delete.html:
"Note: The syntax for multiple-table DELETE statements that use table
aliases changed between MySQL 4.0 and 4.1. In MySQL 4.0, you should use the
true table name to refer to any table from which rows should be deleted:

DELETE test FROM test AS t1, test2 WHERE ...
In MySQL 4.1, you must use the alias:

DELETE t1 FROM test AS t1, test2 WHERE ..."The 4.0 syntax unfortunately does
not work when using self-joins in a multi-table DELETE.There's an ambiguity
because 4.0 does not support deleting from the table named by an alias.If
you cannot upgrade to MySQL 4.1 or later, you may be stuck doing this in two
statements:one query to fetch the primary key value you need to delete, and
the second statement to delete where the primary key value matches this
list.Regards,Bill K.

Re: help with query (delete lowest value)

Bill Karwin wrote:
> wrote in message
> news:1142888719.232371.92970@v46g2000cwv.googlegroups.com...
> > tried the other query and received this:
> > #1066 - Not unique table/alias: 'c1'
>
> Woops. This is probably due to a difference between MySQL 4.0 and 4.1.
> Here's an excerpt from http://dev.mysql.com/doc/refman/4.1/en/delete.html:
> "Note: The syntax for multiple-table DELETE statements that use table
> aliases changed between MySQL 4.0 and 4.1. In MySQL 4.0, you should use the
> true table name to refer to any table from which rows should be deleted:
>
> DELETE test FROM test AS t1, test2 WHERE ...
> In MySQL 4.1, you must use the alias:
>
> DELETE t1 FROM test AS t1, test2 WHERE ..."The 4.0 syntax unfortunately does
> not work when using self-joins in a multi-table DELETE.There's an ambiguity
> because 4.0 does not support deleting from the table named by an alias.If
> you cannot upgrade to MySQL 4.1 or later, you may be stuck doing this in two
> statements:one query to fetch the primary key value you need to delete, and
> the second statement to delete where the primary key value matches this
> list.Regards,Bill K.

i think i will have to do the latter - we don't manage the server.

can two statements be strung together, into one, passing the id to the
second as a variable? or, i could just do it in php, etc.

thanks,
s7

Re: help with query (delete lowest value)

wrote in message
news:1142894546.975946.179250@j33g2000cwa.googlegroups.com.. .
> can two statements be strung together, into one, passing the id to the
> second as a variable? or, i could just do it in php, etc.

The PHP function mysql_query() executes only one SQL statement at a time.
So I would recommend doing this in two calls to mysql_query(). Use a PHP
variable to store the result.

Another option is the improved MySQL extension mysqli for PHP 4.1.3 or
higher. This extension includes a function mysqli_multi_query():
http://us2.php.net/manual/en/function.mysqli-multi-query.php
But you may not have access to the mysqli extension, on the host on which
you are running PHP scripts.

Anyway, I'm not sure there is a way in MySQL 4.0 to store the results of a
SELECT into a MySQL variable that can be used in a subsequent statement.
The "SET" statement takes an expression argument, but I don't think that
expression can contain a query.
http://dev.mysql.com/doc/refman/4.1/en/set-option.html

Regards,
Bill K.