So I have a table called X with columns A,B,C. If B = CertainResult, then I need to assign half of the table with that CertainResult one value, and half another value.<
>
<
>
Any idea how to do it with SQL?<
>
<i></i>

That should say... and the other half with CertainResult another value instead of 'half another value' <i></i>

Easy with a stored procedure, don't know how to do it with a runtime query. If you have to do it runtime, two queries would be easy enough.<
>
<
>
UPDATE X SET D='New Value' WHERE B='CertainResult'<
>
UPDATE X SET D='Other Value' WHERE B NOT 'CertainResult' ---------------------------------<
>
Burog Warrior of Oryx<i></i>

Not necessarily. Doing that would update all of CertainResult to New Value. The second update wouldn't do anything because CertainResult does not exist anymore.<
>
<
>
Let me clarify.<
>
<
>
I have multiple entries for B.. one would be CertainResult, another would be AResult, BResult, DResult, etc... I need to take half of the CertainResult's, change them to say NewValue, and the other half of the CertainResults and change them to OtherValue <
>
<i></i>

Run a loop <i></i>

I usually take the easy way out, pull all the data into arrays using getrows, then figure out what and when to update.<
>
<
>
But as long as you don't update B, you can use those two queries all you want. ---------------------------------<
>
Burog Warrior of Oryx<i></i>

First off, I'm assuming this is for homework. If so, just post the whole problem. <
>
Second, there are a multitude of ways to do it, but it depends on how the table is configured. For example, is there a primary key? If not, it's impossible to do it in a single SQL statement. If so, is it a sequencial primary key? If so, you could just do an odd/even deal. There are a lot of options, but it really depends on how the table is defined.<
>
<
>
The easiest option (but probably least efficient) would just be to define an updatable cursor for SELECT * FROM X WHERE B<
>
Then just step through and alternate doing UPDATE WHERE CURRENT OF.<
>
<
>
The most efficient way would be to use a hash function that returns NewValue 50% of the time and OtherValue 50% of the time. For example, if A was a sequence number:<
>
UPDATE X<
>
SET B = (CASE WHEN ((A%2)=0) THEN NewValue ELSE OtherValue END)<
>
WHERE B = CurrentValue<
>
<
>
If you post more specifics, we might be able to help.<
>
However, with the vague requirements, a cursor is the only option. <i></i>

Quote:<
>
First off, I'm assuming this is for homework. If so, just post the whole problem. <
>
<
>
<
>
Actually not for homework. For actual work for a University. I could write a VB program that'll count the number of entries, loop and change half of the entries. Problem is, I'm 4 hours away from there and don't have time to walk these people through all of it.<
>
<
>
Whole situation then:<
>
<
>
2 Tables<
>
<
>
Table one: Participants (Column - MajorAb
, DayTwo, Participant_ID PK.. sequential)<
>
<
>
I just simplified it into one table, even though there are two tables to ease this situation. MajorAb
could be a multitude of things.. GE, US, ENSC, BIOL, etc.<
>
<
>
DayTwo is a place they need to be on.. Day Two *One* major wants to be different and take half of the people at a certain time and half at another time.. So that one major is screwing me up.... 'US'<
>
<
>
Half of the 'US' majors need to be in at 8am, the other at 10am.<
>
<
>
The A%0=0/then/else may have worked, except there could be a 80/20 ratio of people with odd/even p_ID's.<
>
<
>
I might just have to do this in a loop through VB or something <i>Edited by: FalorEQ at: 6/20/05 11:18 pm<
></i>