update field with increment values

On Tue, 01 Jan 2008 14:07:54 +0100, php_mysql_beginer911
wrote:

> Hi everyone ..
>
> i am wondering if it is possible to update a field where filed values
> are null
>
> with increment values
>
> i can do same in php by using a loop and updating a field
>
> for($i=1;$i<=mysql_num_rows($result);$i++ )
> {
> $sql="UPDATE table SET col=$i ORDER BY col_date DESC";

I don't think that's what you want...

> }
>
> is it possible wihout using any php loop
>
> i use when fields have values
>
> update table set col=col+1 where .....
>
> but couldn't find anywhere how to do when values are 0 or null with
> only mysql query

I'm going to guess this is what you want:

SET @myVar := 0;
UPDATE table SET col = @myVar := @myVar + 1 ORDER BY col_date DESC;

So, just 2 'queries' to call in MySQL.
--
Rik Wasmus

Rik Wasmus wrote:
> On Tue, 01 Jan 2008 14:07:54 +0100, php_mysql_beginer911
> wrote:
>
>> Hi everyone ..
>>
>> i am wondering if it is possible to update a field where filed values
>> are null
>>
>> with increment values
>>
>> i can do same in php by using a loop and updating a field
>>
>> for($i=1;$i<=mysql_num_rows($result);$i++ )
>> {
>> $sql="UPDATE table SET col=$i ORDER BY col_date DESC";
>
> I don't think that's what you want...
>
>> }
>>
>> is it possible wihout using any php loop
>>
>> i use when fields have values
>>
>> update table set col=col+1 where .....
>>
>> but couldn't find anywhere how to do when values are 0 or null with
>> only mysql query
>
> I'm going to guess this is what you want:
>
> SET @myVar := 0;
> UPDATE table SET col = @myVar := @myVar + 1 ORDER BY col_date DESC;
>
> So, just 2 'queries' to call in MySQL.
> --Rik Wasmus


It should work just fine if the column value is 0, NULL is the issue.
Since NULL is not a value there is nothing to add 1 to. I would have to
say this is the expected behavior. But, I suppose it would be ok if it
worked that way too.



--
Norman
Registered Linux user #461062

On Wed, 02 Jan 2008 17:21:26 +0100, Norman Peelman
wrote:

> Rik Wasmus wrote:
>> On Tue, 01 Jan 2008 14:07:54 +0100, php_mysql_beginer911
>> wrote:
>>
>>> Hi everyone ..
>>>
>>> i am wondering if it is possible to update a field where filed values
>>> are null
>>>
>>> with increment values
>>>
>>> i can do same in php by using a loop and updating a field
>>>
>>> for($i=1;$i<=mysql_num_rows($result);$i++ )
>>> {
>>> $sql="UPDATE table SET col=$i ORDER BY col_date DESC";
>> I don't think that's what you want...
>>
>>> }
>>>
>>> is it possible wihout using any php loop
>>>
>>> i use when fields have values
>>>
>>> update table set col=col+1 where .....
>>>
>>> but couldn't find anywhere how to do when values are 0 or null with
>>> only mysql query
>> I'm going to guess this is what you want:
>> SET @myVar := 0;
>> UPDATE table SET col = @myVar := @myVar + 1 ORDER BY col_date DESC;
>> So, just 2 'queries' to call in MySQL.
>
> It should work just fine if the column value is 0, NULL is the issue.
> Since NULL is not a value there is nothing to add 1 to. I would have to
> say this is the expected behavior. But, I suppose it would be ok if it
> worked that way too.

You're not working with the previous column value, so wether 'col' holds
NULL,0, or 'thisisjustastring', it will just be overwritten with the
integer from @myVar. You're effectively making an entire new 'order' with
this. If previous values have to be taken into account I have
misunderstood the question.
--
Rik Wasmus

Rik Wasmus wrote:
> On Wed, 02 Jan 2008 17:21:26 +0100, Norman Peelman
> wrote:
>
>> Rik Wasmus wrote:
>>> On Tue, 01 Jan 2008 14:07:54 +0100, php_mysql_beginer911
>>> wrote:
>>>
>>>> Hi everyone ..
>>>>
>>>> i am wondering if it is possible to update a field where filed values
>>>> are null
>>>>
>>>> with increment values
>>>>
>>>> i can do same in php by using a loop and updating a field
>>>>
>>>> for($i=1;$i<=mysql_num_rows($result);$i++ )
>>>> {
>>>> $sql="UPDATE table SET col=$i ORDER BY col_date DESC";
>>> I don't think that's what you want...
>>>
>>>> }
>>>>
>>>> is it possible wihout using any php loop
>>>>
>>>> i use when fields have values
>>>>
>>>> update table set col=col+1 where .....
>>>>
>>>> but couldn't find anywhere how to do when values are 0 or null with
>>>> only mysql query
>>> I'm going to guess this is what you want:
>>> SET @myVar := 0;
>>> UPDATE table SET col = @myVar := @myVar + 1 ORDER BY col_date DESC;
>>> So, just 2 'queries' to call in MySQL.
>>
>> It should work just fine if the column value is 0, NULL is the
>> issue. Since NULL is not a value there is nothing to add 1 to. I would
>> have to say this is the expected behavior. But, I suppose it would be
>> ok if it worked that way too.
>
> You're not working with the previous column value, so wether 'col' holds
> NULL,0, or 'thisisjustastring', it will just be overwritten with the
> integer from @myVar. You're effectively making an entire new 'order'
> with this. If previous values have to be taken into account I have
> misunderstood the question.
> --Rik Wasmus

I'm sorry, I meant that as a response to the OP...

--
Norman
Registered Linux user #461062