Update query - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Update query

Author

Topic

sardinka
Posting Yak Master

142 Posts

Posted - 2005-02-09 : 10:02:46

Maybe some one can help me...
I have 2 tables with the following structure:
CREATE TABLE [table1](
[GRID] [int] IDENTITY (1, 1) NOT NULL ,
[GID] [varchar] (10) NOT NULL ,
[RID] [int] NOT NULL ) On primary

CREATE TABLE [table2] (
[RID] [int] IDENTITY (1, 1) NOT NULL ,
[RText] [varchar] (400) NULL
) ON [PRIMARY]

If [RID] in table 1 exists more then one time (the first occurence leave as is)
and then create a new [RID] every time it is found in table2 with the RText from [RID] and update table1 with
applicable [RID] .

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-09 : 10:29:03

some sample data and desired results would be nice to have...

Go with the flow & have fun! Else fight the flow

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-09 : 12:48:45

Not sure what you want.
If the Rid is in table1 then leave the first occurrance (assume you mean with min grid).
What about the other occurrances?
>> create a new [RID] every time it is found in table2
You mean max(rid)+1 from table1 and add the entry?

Sounds a bit like you just want to add the entries from table2 to table1 but I doubt it.

==========================================
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.

sardinka
Posting Yak Master

142 Posts

Posted - 2005-02-09 : 15:32:04

I hope this helps:
Before Update:
table1:
[GRID] [GID] [RID]
102 2 1
103 3 1

table2:
[RID] [RText]
1 Rtext

After Update:
table1:
[GRID] [GID] [RID]
102 2 1
103 3 2

table2:
[RID] [RText]
1 Rtext
2 Rtext

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-09 : 16:38:46

Wouldn't have guessed that was what you wanted and can't think of a reason for it.
What happpens after you have created the rid in table2 if theres already a rid of that number in table1?
Something like


create table #a (grid int, rid int)
insert #a 
select t1.Grid, t1.rid
from table1 t1
join table2 t2
on t1.rid = t2.rid
where t1.grid > (select min(grid) from table1 t3 where t3.rid = t2.rid)

declare @i int, @id int
select @i = 0
while @i < (select max(grid) from #a)
begin
    select @i = min(grid) from #a where grid > @i
    insert table2 (RText)
    select RText
    from Table2
    where rid = (select rid from #a where grid = @i)
    
    select @id = scope_identity()
    
    update table1
    set rid = @id
    where grid = @i
end

==========================================
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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources