SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 update table with next value		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rjhe22
Posting Yak Master

151 Posts
Posted - 10/04/2012 :  06:01:46  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 10/04/2012 :  06:40:59  Show Profile  Reply with Quote
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.
Edited by - sunitabeck on 10/04/2012 06:51:57
Go to Top of Page

rjhe22
Posting Yak Master

151 Posts
Posted - 10/04/2012 :  06:43:28  Show Profile  Reply with Quote
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
Edited by - rjhe22 on 10/04/2012 09:23:46
Go to Top of Page

rjhe22
Posting Yak Master

151 Posts
Posted - 10/05/2012 :  04:52:40  Show Profile  Reply with Quote
anyone any ideas on how this might be done. very stuck on it
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000