SET random value 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!!

 

[PHV]

You may try something like this:

UPDATE Zonal
SET comm_assign2 = IIf(Rnd(mean_elev)<0.5, 26, 27)
WHERE ct_code IN (2053,4206) AND mean_elev BETWEEN 1280 And 1730

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Agentilis]

Thank you PH, that worked great in isolation. Now I'm trying to put it all together in one UPDATE statement. I.E.:

UPDATE Zonal
SET comm_assign2 = 27 WHERE IN (2053,4206) AND (mean_elev < 1280)
SET comm_assign2 = 26 WHERE IN (2053,4206) AND (mean_elev > 1730)
SET comm_assign2 = IIf(Rnd(mean_elev)<0.5, 26, 27)
WHERE ct_code IN (2053,4206) AND mean_elev BETWEEN 1280 And 1730

This returns a syntax error (missing operator). I'm trying various IF and WHEN options but can't crack the solution. Any thoughts?

Thanks!! --The Rookie

 

[BigRed1212]

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

You don't need SET each time.

http://www.w3schools.com/sql/sql_update.asp

 

[Emtucifor]

UPDATE Zonal
SET
   comm_assign2 = Iif(mean_elev < 1280, 26,
      Iif(mean_elev > 1730, 27,
         Iif(Rnd(mean_elev) < 0.5, 26, 27)
      )
   )
WHERE
   ct_code IN (2053,4206)