The following works on 4.0.16, for those of us who'd like to stick to the
stable release:
update controltable set nextid = 1 + (@prevval := nextid)
Note. There was a problem with this in 4.0.13--namely, the value of
@prevval sometimes was garbage. I reported this via the list, but got no
response--however, the problem seems to have gone away in 4.0.14.
-----Original Message-----
From: [EMAIL PROTECTED]
Subject: RE: Setting variables on update
Date: Mon, 23 Feb 2004 11:50:38 -0700
Try this...works on 4.1.2
UPDATE ControlTable SET NextID=3D 1 + (select @prevval :=3D NextID)
Ed
-----Original Message-----
From: Sasha Pachev [mailto:[EMAIL PROTECTED]
Sent: Monday, February 23, 2004 11:19 AM
To: Matt Fagan
Cc: [EMAIL PROTECTED]
Subject: Re: Setting variables on update
Matt Fagan wrote:
> Hello,
>=20
> I'm trying to run an SQL update statement like:
>=20
> UPDATE ControlTable SET @prevval :=3D NextID, NextID =3D NextID
> + 1
>=20
> I've tried using a range of different syntax, but nothing
> seems to work. This is something that does work in another
> SQL server I used to use.
>=20
> Does anybody know if this is something MySQL can do, and if
> so, what is the correct syntax? Note that I can't use an
> autoincrement column because the IDs aren't being put into
> a database.
>=20
> I've searched through MySQL docs and mailing lists but
> couldn't find any mention of this. Any help would be
> appreciated.
A UDF or your own static function might work. Worst case, MySQL source
can be=20
modified to make this work.
--=20
Sasha Pachev
Create online surveys at http://www.surveyz.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
