Solved

mysql query for alternating update

Posted on 2012-03-27
3
262 Views
Last Modified: 2012-03-27
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
0
Comment
Question by:St_Aug_Beach_Bum
  • 2
3 Comments
 
LVL 83

Accepted Solution

by:
Dave Baldwin earned 500 total points
ID: 37774736
You haven't specified an ORDER BY so you can't tell which rows are getting what.  Any deletions and inserts will alter the 'natural' order of the rows in the table because SQL databases don't preserve that order.
0
 

Author Comment

by:St_Aug_Beach_Bum
ID: 37775121
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
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 37775233
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.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will explain how to display the first page of your Microsoft Word documents (e.g. .doc, .docx, etc...) as images in a web page programatically. I have scoured the web on a way to do this unsuccessfully. The goal is to produce something …
Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question