June 26, 2007 at 7:19 am
Hello
I have this following TSQL command and i need to update field INTID with row number for each record :
select cm,cmdesc,intid,rno,rdata from po where cmdesc like'F40%' order by cm,rdata,intid asc
for exemplo:
document nº 1 intid =1
document nº 2 intid =2
.....
In SQL server i don´t have the row number .
Someone have an ideia to do this.
Many thanks
Luis Santos
June 26, 2007 at 7:30 am
You have the row_number() function in SQL 2005 that can be used for your rowid. If you want more information just read the link given below.
http://www.databasejournal.com/features/mssql/article.php/3572301
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
June 26, 2007 at 8:53 am
Hello Sugesh
I test your reply an i put them in an update statment like this :
update po set intid=
(SELECT ROW_NUMBER() OVER (ORDER BY rno,rdata ASC) AS ROWID FROM po poo (nolock) where poo.cm = 115 and poo.cmdesc like 'F40%' and poo.postamp=po.postamp) from po
where po.cm = 115 and po.cmdesc like 'F40%'
But my field intid as always the same value = 1 Why??
Can you give me some help ?
Many thanks
Luis Santos
June 26, 2007 at 4:13 pm
try:
update d set intid= data.rnumb
(SELECT ROW_NUMBER() OVER (ORDER BY rno,rdata ASC) AS rnumb , cm, postamp
FROM po poo (nolock)
where poo.cm = 115 and poo.cmdesc like 'F40%') data
inner join po d on d.cm = data and d.postamp= data.postamp
where d.cm = 115 and d.cmdesc like 'F40%'
* Noel
June 26, 2007 at 4:34 pm
Hello Noeld
Thanks for your reply, but when i run your TSQL they return errors :
Your TSQL:
update d set intid= data.rnumb
(SELECT ROW_NUMBER() OVER (ORDER BY rno,rdata ASC) AS rnumb , cm, postamp
FROM po poo (nolock)
where poo.cm = 115 and poo.cmdesc like 'F40%') data
inner join po d on d.cm = data and d.postamp= data.postamp
where d.cm = 115 and d.cmdesc like 'F40%'
37000(156)[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'SELECT'.
37000(102)[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near ')'.
37000(8180)[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (0,03 secs)
June 26, 2007 at 4:41 pm
Hello again Noeld
I detect the problem, you have forget the "from" clause. I test with the following update statment and they work fine.
Many thanks for your interest.
Best regards
Luis Santos
update d set intid= data.rnumb "FROM"
(SELECT ROW_NUMBER() OVER (ORDER BY rno,rdata ASC) AS rnumb , cm, postamp
FROM po poo (nolock)
where poo.cm = 115 and poo.cmdesc like 'F40%') data
inner join po d on d.cm = data and d.postamp= data.postamp
where d.cm = 115 and d.cmdesc like 'F40%'
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply