asked on

mysql query for alternating update

I need to update a table so that the field 'series' alternates between 'a' and 'b'.

Something like this:
------------
include ("/home/mysite/connect.inc");
mysql_select_db(oys) or die("Unable to load update into mysql");

$query = "UPDATE oys_sites SET `series`=

//at this point, I need the 'series' field in the first row set to 'a', the next row set to 'b', then 'a', then 'b', alternating, until all rows are done.
-------

This will be entered into a php script so I can set it when needed.

Thanks for any help writing this up,

Chris

ASKER CERTIFIED SOLUTION

Dave Baldwin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Chris Andrews

ASKER

Ok, I've got it

Worked it out by building a loop and incrementing through it and updating one row at a time, using the increment count as a way to switch back and forth between 'a' and 'b'.

Thanks for the suggestion Dave, I added a note here to say I wasn't quite following you on that and to say it was existing entries I was trying to affect - but that note didn't seem to show up here. Maybe I messed something up.

Thank you for the attempt though and I will award you points for trying to help :)

Chris

Dave Baldwin

Since you posted an UPDATE statement, I assumed existing entries. What I was trying to point out was that without using an ORDER BY statement to set the order, you are unable to 'guarantee' that the alternating order will be preserved in the future. On the other hand, if the table isn't too big, you can just run that code again to reset it.