Re: SQL syntax to update records with diff'rent function generated random PINS

From: Garp (garp7_at_no7.blueyonder.co.uk)
Date: 04/12/04 

Date: Mon, 12 Apr 2004 20:41:24 GMT

"John Pastrovick" <pastrovic2k@hotmail.com> wrote in message
news:a21852f1.0404121212.21899286@posting.google.com...
> I use a function, myrandomPIN (), to generate random PIN numbers.
>
> The following sql query updates records with the SAME PIN number but.
> I want to generate DIFFERENT pin numbers for every record. The
> function is ok but I can't figure out how to run it individually for
> each record. In other words I do not know the correct syntax to use
> UPDATE in a loop (if necessary) so that a different call to the
> function is done every time or ecah record ends up with a different
> PIN.
>
> Or may be this can be done with a single mysql_query($sql2)???
>
> -------------------------------------------------------------------------- 

--
> $qr = @mysql_query($sqlb1) or die("ERROR");
> $rs1 = mysql_fetch_array($qr);
>
>
> $thepin=myrandomPIN();
> $sql2="UPDATE clients SET pin='$thepin' WHERE code='".$thecode."'";
> $qr = @mysql_query($sql2) or die("ERROR"");
> --------------------------------------------------------------------------
>
> Thanks
The important thing about your UPDATE statement is the WHERE clause - since
you only retrieve one record (I hope!), you can only update one at a time
this way.
For multiple updates of rows like this pseduocode:
    update set (a=1 where id=1, a=2 where id=2)
you'd need to create and execute a temporary stored procedure (MySQL 5 and
up - http://dev.mysql.com/doc/mysql/en/Stored_Procedures.html). If you know
them, it's easy enough, but if you don't, stick to your original loop, it's
no hardship.
Garp