best way to...

hi all,

I want to decrement all the rows in a column that contain a number that is
bigger then one defined by me.

Is there some function in mysql or php that makes this task easy or do I
have to check row per row and if true decrement the row?

kind regards
Stijn

re: best way to...

On Fri, 14 Nov 2003 18:57:25 +0100, Stijn Goris wrote:
[color=blue]
> hi all,
>
> I want to decrement all the rows in a column that contain a number that is
> bigger then one defined by me.
>
> Is there some function in mysql or php that makes this task easy or do I
> have to check row per row and if true decrement the row?
>
> kind regards
> Stijn[/color]


$sql = "
UPDATE database.table
SET field = field - 1
WHERE field > '$max'
";


Untested.. but should work =)



Regards,

Ian

--
Ian.H [Design & Development]
digiServ Network - Web solutions
www.digiserv.net | irc.digiserv.net | forum.digiserv.net
Programming, Web design, development & hosting.

re: best way to...

Stijn Goris wrote:
[color=blue]
> I want to decrement all the rows in a column that contain a number that is
> bigger then one defined by me.
>
> Is there some function in mysql or php that makes this task easy or do I
> have to check row per row and if true decrement the row?
>
> kind regards
> Stijn
>
>[/color]

Not sure if this is what you're looking for, but by the sounds of it,
you can do this with a simple update query...

UPDATE table_name SET field_name = field_name -1 WHERE field_name >
my_number

--
Justin Koivisto - spam@koivi.com
PHP POSTERS: Please use comp.lang.php for PHP related questions,
alt.php* groups are not recommended.

re: best way to...

Justin Koivisto wrote:
[color=blue]
> Stijn Goris wrote:
>[color=green]
>> I want to decrement all the rows in a column that contain a number
>> that is
>> bigger then one defined by me.
>>
>> Is there some function in mysql or php that makes this task easy or do I
>> have to check row per row and if true decrement the row?
>>
>> kind regards
>> Stijn
>>
>>[/color]
>
> Not sure if this is what you're looking for, but by the sounds of it,
> you can do this with a simple update query...
>
> UPDATE table_name SET field_name = field_name -1 WHERE field_name >
> my_number
>[/color]

Once again, I should have checked for newly posted messages before
replying.... ;)

--
Justin Koivisto - spam@koivi.com
PHP POSTERS: Please use comp.lang.php for PHP related questions,
alt.php* groups are not recommended.

re: best way to...

"Ian.H" <ian@WINDOZEdigiserv.net> wrote in message
news:pan.2003.11.14.18.04.37.361478@hybris.digiser v.net...[color=blue]
> On Fri, 14 Nov 2003 18:57:25 +0100, Stijn Goris wrote:
>[color=green]
> > hi all,
> >
> > I want to decrement all the rows in a column that contain a number that[/color][/color]
is[color=blue][color=green]
> > bigger then one defined by me.
> >
> > Is there some function in mysql or php that makes this task easy or do I
> > have to check row per row and if true decrement the row?
> >
> > kind regards
> > Stijn[/color]
>
>
> $sql = "
> UPDATE database.table
> SET field = field - 1
> WHERE field > '$max'
> ";
>
>
> Untested.. but should work =)
>
>
>
> Regards,
>
> Ian
>
> --
> Ian.H [Design & Development]
> digiServ Network - Web solutions
> www.digiserv.net | irc.digiserv.net | forum.digiserv.net
> Programming, Web design, development & hosting.
>[/color]

thanks,

I didn't know sql could do those operations.

thanks

re: best way to...

Justin Koivisto (31.596% quality rating):[color=blue]
>
> UPDATE table_name SET field_name = field_name -1 WHERE field_name >
> my_number[/color]

Note: if field_name is a unique field or key, you'll need to add an
ORDER BY clause to make sure this works.

/joe
--
In City Cafe, Brian Culver celebrates with the nameserver from Dramatech,
and then emasculates the lethal, aggravating frat house from the Deck(tm).
In 101, Keith links to the website of Kramer for the fiber channel, and
then stimulates a gombizler. Scott Watkins felches Taco ... [tape runs out]

re: best way to...

On Fri, 14 Nov 2003 18:45:06 +0000 (UTC), Disco Plumber
<scag@moralminority.org> wrote:
[color=blue]
>Justin Koivisto (31.596% quality rating):[color=green]
>>
>> UPDATE table_name SET field_name = field_name -1 WHERE field_name >
>> my_number[/color]
>
>Note: if field_name is a unique field or key, you'll need to add an
>ORDER BY clause to make sure this works.[/color]

But watch out if you're still using MySQL 3.x, as it doesn't support ordered
updates:

mysql> insert into test values (2), (3), (1);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> update test set id = id + 1;
ERROR 1062: Duplicate entry '3' for key 1
mysql> update test set id = id + 1 order by id;
ERROR 1064: You have an error in your SQL syntax near 'order by id' at line 1

--
Andy Hassall (andy@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)