Is it possible to use some type of option in an update statement so that
only a single row (arbitrarily chosen) will get updated in the event that
there are two rows matching a where specification?

For example, in the following, if table W1 contains two rows where QN = 2,
then both are changed to the value 3. I just want 1 of them changed to 3 and
it doesn't matter which.

Update W1 set QN = 2 where QN = 3
Thanks







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

Hi,

Jeff wrote:

> I just want 1 of them changed to 3 and it doesn't matter which.

....LIMIT 1

"Andreas Stieger" <Andreas.Stieger@gmx.de> wrote in message
news:47a96974$0$9109$9b4e6d93@newsspool2.arcor-online.net...
> Hi,
>
> Jeff wrote:
>
>> I just want 1 of them changed to 3 and it doesn't matter which.
>
> ...LIMIT 1

....still not working. The first query below does not work (mysql 5.22) with
the alter command, but the second does with select. Any ideas?


alter mytable set QN = 2 where QN = 3 limit 1; ** doesn't work **

select QN from mytable where QN = 3 limit 1; ** does work **




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

On Wed, 06 Feb 2008 22:59:16 +0100, Jeff <no_one@george.com> wrote:

>
> "Andreas Stieger" <Andreas.Stieger@gmx.de> wrote in message
> news:47a96974$0$9109$9b4e6d93@newsspool2.arcor-online.net...
>> Hi,
>>
>> Jeff wrote:
>>
>>> I just want 1 of them changed to 3 and it doesn't matter which.
>>
>> ...LIMIT 1
>
> ...still not working. The first query below does not work (mysql 5.22)
> with
> the alter command, but the second does with select. Any ideas?
>
>
> alter mytable set QN = 2 where QN = 3 limit 1; ** doesn't work **
>
> select QN from mytable where QN = 3 limit 1; ** does work **

Euhm, UPDATE statement, not ALTER....

UPDATE `mytable` SET `QN` = 2 WHERE `QN` = 3 LIMIT 1;
--
Rik Wasmus

On Thu, 07 Feb 2008 02:23:27 +0100, Rik Wasmus
<luiheidsgoeroe@hotmail.com> wrote:

> On Wed, 06 Feb 2008 22:59:16 +0100, Jeff <no_one@george.com> wrote:
>
>>
>> "Andreas Stieger" <Andreas.Stieger@gmx.de> wrote in message
>> news:47a96974$0$9109$9b4e6d93@newsspool2.arcor-online.net...
>>> Hi,
>>>
>>> Jeff wrote:
>>>
>>>> I just want 1 of them changed to 3 and it doesn't matter which.
>>>
>>> ...LIMIT 1
>>
>> ...still not working. The first query below does not work (mysql 5.22)
>> with
>> the alter command, but the second does with select. Any ideas?
>>
>>
>> alter mytable set QN = 2 where QN = 3 limit 1; ** doesn't work **
>>
>> select QN from mytable where QN = 3 limit 1; ** does work **
>
> Euhm, UPDATE statement, not ALTER....
>
> UPDATE `mytable` SET `QN` = 2 WHERE `QN` = 3 LIMIT 1;

Oh, and for a more random row to be altered:
UPDATE `mytable` SET `QN` = 2 WHERE `QN` = 3 ORDER BY RAND() LIMIT 1;

see: <http://dev.mysql.com/doc/refman/5.1/en/update.html>
--
Rik Wasmus

"Rik Wasmus" <luiheidsgoeroe@hotmail.com> wrote in message
newsp.t54rhdgk5bnjuv@metallium.lan...
On Wed, 06 Feb 2008 22:59:16 +0100, Jeff <no_one@george.com> wrote:

>
> "Andreas Stieger" <Andreas.Stieger@gmx.de> wrote in message
> news:47a96974$0$9109$9b4e6d93@newsspool2.arcor-online.net...

Euhm, UPDATE statement, not ALTER....

UPDATE `mytable` SET `QN` = 2 WHERE `QN` = 3 LIMIT 1;
--
Rik Wasmus

....it's always some simple typo that screws me up. I was using "update" in
the original code and for some reason was typing "alter" when I was testing
the "limit" option - thinking that the only thing I changed was the latter
when I actually typed in the wrong main command.

Thanks

Jeff



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