next record - 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

Transact-SQL (2000)

next record

Author

Topic

mdhingra01
Posting Yak Master

179 Posts

Posted - 2006-09-05 : 11:03:59

Table A has (filenumber, line, code1,code2,code3) columns. In Table A, a filenumber can have multiple lines (1-99), and the code columns associated with each filenumber can change on the different lines.

Therefore for example...

Filenumber Line Code1 Code2 Code3
001 1 8850 0300 NULL
002 1 8545 0030 NULL
002 2 4300 0001 NULL
002 3 2500 0000 NULL

I need help writing a query to populate another table. In Table B Iwant to have all the records from Table A, however, when code1=8545 and line=1 then I want to update code3=code1 AND update code1 on line=1 to equal code1 on line2.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-09-05 : 11:09:44

update B
set code1 = t2.code1, code3 = t1.code1
from TableB B
join TableB t2
on B.Filenumber = t2.Filenumber
and B.Line = 1
and t2.Line = 2

If you want it for all the next lines then change
and B.Line = 1
and t2.Line = 2

to B.Line + 1 = t2.Line

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