April 28, 2009 at 11:50 am
Hi all,
I have one table witch contains a column int. For this column , i need to find the next value from one ID in another table and update this column. I have to do this set based.
Example
Table 1
Id, Name
AAAAA
BBBBBB
CCCCC
DDDDD
Table 2
maxid
10
So i want to update ID in table 1 always with the last value + 1..
Id, Name
10 AAAAA
11 BBBBBB
12 CCCCC
13 DDDDD
but in table 2 the last value is 10...when i finished the update in table 1 I update the maxid in table 2 (in this case with 13
There wayt to do this using CTE´s ?
April 28, 2009 at 2:34 pm
update table2
set maxID = (select max(ID)+1 from table1)
--
Are you intending to put this in a trigger or what? That seems like a lot of trouble.
Why aren't you simply using IDENTITY(1,1) and forget all about table2?
I have to do this set based.
Is this a homework assignment or an interview question?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 28, 2009 at 2:35 pm
If you can't make it an identity, then take a look at Row_Number().
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 28, 2009 at 2:38 pm
April 29, 2009 at 7:38 am
You're welcome. Glad we could help.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply