Am new at T-SQL and need some pointers

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Am new at T-SQL and need some pointers

Author

Topic

Dagaz
Starting Member

7 Posts

Posted - 2004-11-14 : 07:17:50

I am updating a whole table of records but am adding some randomness to it, this is wat i got so far, so where am i going wrong lol

Declare @NewForm int

Update ViewWeeklyFormUpdate Set Form =

select @FormUpd = round((StableHands/22) + (RAND() * (2-0)),0)

If @FormUpd = 0
Begin
select @NewForm = Form - 1
End

If @FormUpd = 1
Begin
select @NewForm = Form
End

If @FormUpd = 2
Begin
select @NewForm = Form + 1
End

select @NewForm

Dagaz

nr
SQLTeam MVY

12543 Posts

Posted - 2004-11-14 : 13:12:15

Depends what you are trying to do.
Update all recs in ViewWeeklyFormUpdate

Update ViewWeeklyFormUpdate
Set Form = round((StableHands/22) + (RAND() * (2-0)),0)
(note this will use the same value for RAND() for each record.

or return a value in @NewForm

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Dagaz
Starting Member

7 Posts

Posted - 2004-11-14 : 13:39:06

ah no i want a random for each record, eg if the random val = 0 then form = form -1

rand = 1 stays same

rand = 2 form + 1 but it is also infulenced by a linked table thus the view.

Dagaz

nr
SQLTeam MVY

12543 Posts

Posted - 2004-11-14 : 13:50:50

You need to understand a bit more about rand() - have a look in bol and try

create table #a (id int, i float)
insert #a select id, 0 from sysobjects
update #a set i = rand()
select * from #a
drop table #a

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X002548
Not Just a Number

15586 Posts

Posted - 2004-11-15 : 10:31:48

quote:
Originally posted by Dagaz

I am updating a whole table of records but am adding some randomness to it

OK, I'm officially lost....huh?

Brett

8-)

Dagaz
Starting Member

7 Posts

Posted - 2004-11-15 : 11:07:42

sorry lol, will post full detaisl when i get home or at least try

Dagaz

Dagaz
Starting Member

7 Posts

Posted - 2004-11-15 : 12:27:08

OK here is how it goes,

i have a table called TblOwners, which has integer StableHands

each "Owner" can have many horses, in TblHorses (another integer as PK and FK)

once a week i want to run a statement that goes through the horses and updates their form (a field) which can be 1 or 2 or 3 or 4 or 5

but the change is a combination of a random number and the amount of stable hand they have.

basically the formula is round(StableHands/22) + (Rand()-(2-0),0)

i want a random number to be either 0 or 1 or 2 but influenced by the stablehands as u can see divided by 22

so if it is 0 then the horse form becomes exising form -1
if it is 1 then form stays the same
if it is 2 then it is existing form + 1

but within the 1-5 range, so if 1 cannot get any lower and if 5 cant get any higher.

Dagaz

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources