help with query (delete lowest value)

[starman7]

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?

[Bill Karwin]

<st******@hotmail.com> wrote in message
news:11**********************@z34g2000cwc.googlegr oups.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/pro.../taskschd.mspx
However, in my experience, Task Scheduler is broken in Windows XP SP2; see
http://support.microsoft.com/default...b;en-us;884573

You can also get cron for Windows in Microsoft's free "Services for UNIX"
product.
http://www.microsoft.com/windowsserv...ds/default.asp

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

Regards,
Bill K.

[starman7]

Bill Karwin wrote:

<st******@hotmail.com> wrote in message
news:11**********************@z34g2000cwc.googlegr oups.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/pro.../taskschd.mspx
However, in my experience, Task Scheduler is broken in Windows XP SP2; see
http://support.microsoft.com/default...b;en-us;884573

You can also get cron for Windows in Microsoft's free "Services for UNIX"
product.
http://www.microsoft.com/windowsserv...ds/default.asp

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

Regards,
Bill K.

[starman7]

tried the other query and received this:

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

shoud as c1 be as 'something else'?

[starman7]

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

[starman7]

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

[Bill Karwin]

<st******@hotmail.com> wrote in message
news:11*********************@v46g2000cwv.googlegro ups.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.

[starman7]

Bill Karwin wrote:

<st******@hotmail.com> wrote in message
news:11*********************@v46g2000cwv.googlegro ups.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

[Bill Karwin]

<st******@hotmail.com> wrote in message
news:11**********************@j33g2000cwa.googlegr oups.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/functio...ulti-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.