newby - random in UPDATE

[Agentilis]

Trying to randomly assign values to records identified with an UPDATE statement. I know values for some conditions as in:

UPDATE Zonal SET comm_assign2 = 27 WHERE (ct_code = 2053 or ct_code = 4206) AND (mean_elev < 1280)
UPDATE Zonal SET comm_assign2 = 26 WHERE (ct_code = 2053 or ct_code = 4206) AND (mean_elev > 1730)

but if mean_elev is >= 1280 and <= 1730 I'd like to randomly SET 50% of records to comm_assign2 = 26 and 50% to comm_assign2 = 27.

Ignoring my feeble programming here, what would be the next statement? Thanks for your help!!

 

[gmmastros]

Try this:

UPDATE Zonal 
SET    comm_assign2 = Case When mean_elev < 1280 Then 27
                           When mean_elev > 1730 Then 27
                           When mean_elev Between 1280 and 1730
                                Then Case When Convert(Char(36), NewID()) < '9' 
                                          Then 27 
                                          Else 26 End
                           End
WHERE (ct_code = 2053 or ct_code = 4206)

Note that this works by generating a guid for each matching row. We effectively check the first character for the guid, which has a range of 0 to F. If the first character is less the '9', we use on value (27), otherwise we use the other value (26).

Also notice that I combined both of your current update statements and the new one. By running this one update query, instead of 3 updates, you will get the same results and it will execute faster.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Agentilis]

Thank you George,

Looks great and taught me a lesson. However it returns a Syntax Error (missing operator) in query expression 'CASE When.....

 

[gmmastros]

I don't see any problems with it. Maybe you can copy/paste the code back in to here so I can take another look.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Agentilis]

Seems straight forward; I'm entering this into a query in MS Access 2007:

UPDATE Zonal_BaseLC_ElevAspect
SET comm_assign2 = Case When mean_elev < 1280 Then 27
When mean_elev > 1730 Then 26
When mean_elev Between 1280 and 1730
Then Case When Convert(Char(36), NewID()) < '9'
Then 27
Else 26 End
End
WHERE (ct_code = 2053 or ct_code = 4206)

 

[markros]

This is SQL Server forum, not Access. In Access there is IIF function instead of CASE statements. However, I'm not sure what is used instead of NewId().

I think you may have better luck asking in Access forum. If you do, please post a thread reference here.

PluralSight Learning Library

 

[Agentilis]

silly newbies ... follow the story at:

http://www.tek-tips.com/viewthread.cfm?qid=1610309&page=1