Update Alternating 0 then 1

[Jusenkyo]

Hello all

I want to run a SQL update statement in VB that will loop and update a column in a table with first 1, then 0, then 1, etc...

So the table will have:
ID Loop
1----1
2----0
3----1
4----0
5----1

Hope that makes sense!
Cheers
J

 

[Remou]

Hi
Your statement above implies that the ID is a unique, positive, sequential number. If so this might work.
Query 1:
UPDATE tblTable SET tblTable.Loop = "1"
WHERE ((([recno]/2-Int([recno]/2))>0));
Query 2:
UPDATE tblTable SET tblTable.Loop = "0"
WHERE ((([recno]/2-Int([recno]/2))=0));

 

[PHV]

And what about this ?
UPDATE yourTable SET Loop = (ID Mod 2);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Jusenkyo]

Remou - Is this code for 2 seperate queries here?
What is recno?

 

[Remou]

Oops, Should have said ID. Sorry about that. Yes, two separate queries.
UPDATE tblTable SET tblTable.Loop = "1"
WHERE ((([ID]/2-Int([ID]/2))>0));
Query 2:
UPDATE tblTable SET tblTable.Loop = "0"
WHERE ((([ID]/2-Int([ID]/2))=0));

But the suggestion from PHV looks more elegant.

 

[Jusenkyo]

It does doesnt it!
I wanted to run this code in VB, so the less code the better really...

What does (ID MOD 2) do?