I'm new to sql and mysql, so forgive me if I'm not asking this in the best
way.


I have a table with a column that should contain a list of integers from 1
to the number of rows that identify the order of the rows as they should
appear elsewhere in an application. I need to do some error checking to see
if all numbers are present or if there are any missing or any duplicates.
That's easy enough through the application, but don't know how to write the
sql to correct the problem if it is found. Could someone tell me how to
change the original column below into the desired one?

Thanks

Jeff

original
1
2
3
3
5
8


desired
1
2
3
4
5
6



--
Posted via a free Usenet account from http://www.teranews.com

On Feb 6, 10:09 pm, "Jeff" <no_...@george.com> wrote:
> I'm new to sql and mysql, so forgive me if I'm not asking this in the best
> way.
>
> I have a table with a column that should contain a list of integers from 1
> to the number of rows that identify the order of the rows as they should
> appear elsewhere in an application. I need to do some error checking to see
> if all numbers are present or if there are any missing or any duplicates.
> That's easy enough through the application, but don't know how to write the
> sql to correct the problem if it is found. Could someone tell me how to
> change the original column below into the desired one?
>
> Thanks
>
> Jeff
>
> original
> 1
> 2
> 3
> 3
> 5
> 8
>
> desired
> 1
> 2
> 3
> 4
> 5
> 6
>
> --
> Posted via a free Usenet account fromhttp://www.teranews.com

Assuming you have a table called orders(id*,orderdate,customer)

CREATE TABLE temp AS
SELECT COUNT(t2.id),t1.orderdate,t1.customer
FROM orders AS t1
LEFT JOIN orders AS t2
ON t2.id <= t1.id
GROUP BY t1.id;

On Wed, 06 Feb 2008 23:09:08 +0100, Jeff <no_one@george.com> wrote:

>
> I'm new to sql and mysql, so forgive me if I'm not asking this in the
> best
> way.
>
>
> I have a table with a column that should contain a list of integers from
> 1
> to the number of rows that identify the order of the rows as they should
> appear elsewhere in an application. I need to do some error checking to
> see
> if all numbers are present or if there are any missing or any duplicates.
> That's easy enough through the application, but don't know how to write
> the
> sql to correct the problem if it is found. Could someone tell me how to
> change the original column below into the desired one?
>
> Thanks
>
> Jeff
>
> original
> 1
> 2
> 3
> 3
> 5
> 8
>
>
> desired
> 1
> 2
> 3
> 4
> 5
> 6

SET @myvar := 0;
UPDATE tablname SET fieldname = @myvar:= @myvar + 1 ORDER BY fieldname;
--
Rik Wasmus

"Rik Wasmus" <luiheidsgoeroe@hotmail.com> wrote in message
newsp.t54rcgks5bnjuv@metallium.lan...
On Wed, 06 Feb 2008 23:09:08 +0100, Jeff <no_one@george.com> wrote:

>SET @myvar := 0;
UPDATE tablname SET fieldname = @myvar:= @myvar + 1 ORDER BY fieldname;
--
Rik Wasmus


Yup. It worked. So simple.

Thanks

Jeff



--
Posted via a free Usenet account from http://www.teranews.com