Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

mysql query for alternating update

Posted on 2012-03-27
3
Medium Priority
?
272 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 84

Accepted Solution

by:
Dave Baldwin earned 2000 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 84

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.
Suggested Courses

604 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