update table with next value - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Administration

update table with next value

Author

Topic

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2012-10-04 : 06:01:46

hi i have 2 tables
in 1 i have the following
rateN1 rateN2 rateN3 rate
a aa aaa 1
b bb bbb 2
c cc ccc 4

table 2 has the following
rateAge rateN rate y/n
mood a 1 n
fitch bb 2 y

so what i need to happen is when something in table 2 is y then it updates with next biggest value.
so in above example when i do my update i need fitch rate to change to 4
how can this be done

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-04 : 06:40:59

You can do it in a single step, but the following two-step code is probably simpler and easier to understand.

DECLARE @nextRate INT;
SELECT @nextRate = MAX(rate) FROM Table1;

UPDATE  Table2 SET rate = @nextRate
WHERE [y/n] = 'y'
-- and rate < @nextRate --??

Edit: If there is the possibility that multiple clients would be doing simultaneous updates, what I posted above would not be the best approach.

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2012-10-04 : 06:43:28

ok thanks thats great just one thing
if for instance the table is like this
rateN1 rateN2 rateN3 rate
a aa aaa 1
b bb bbb 2
c cc ccc 4
d dd ddd 6

table 2 has the following
rateAge rateN rate y/n
mood a 1 n
fitch bb 2 y

but i want it to be the 4 as thats the next number up from the 2 will that sql still work

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2012-10-05 : 04:52:40

anyone any ideas on how this might be done. very stuck on it

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources