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

Transact-SQL (2000)

update query

Author

Topic

wawansur
Starting Member

44 Posts

Posted - 2009-09-17 : 22:50:45

quote:

Table name : J_R

FNTrans FCOA FSeqTime FPostID
A0003 5100-001 0x000000000025CB4A 2435450
A0003 1104-001 0x000000000025522D 2435450
A0003 4100-001 0x00000000002599F9 2435450
A0003 1103-201 0x000000000025ABD5 2435450
A0004 5100-001 0x00000000002575DE 2435450
A0004 1104-001 0x0000000000257667 2435450
A0004 1103-201 0x0000000000257F37 2435450
A0004 4101-001 0x000000000025626D 2435450
A0005 1103-201 0x0000000000257AE3 2435450
A0005 4100-001 0x0000000000257B85 2435450
A0005 5100-001 0x000000000025CBE3 2435450
A0005 1104-001 0x000000000025CBE4 2435450
A0006 1104-001 0x000000000025CBE5 2435450
A0006 1103-301 0x000000000025CBE6 2435450
A0006 4100-001 0x00000000002579CF 2435450
A0006 5100-001 0x000000000025AFB1 2435450
A0019 4100-001 0x0000000000259080 2435450
A0019 5100-001 0x0000000000255B40 2435450
A0019 1104-003 0x0000000000255B41 2435450
A0019 1103-201 0x0000000000255B42 2435450
A0020 1104-001 0x000000000025A870 2435450
A0020 1103-201 0x000000000025A873 2435450
A0020 4100-001 0x000000000025CBAB 2435450

FNTrans : varchar(30)
FCOA : varchar(30)
FSeqtime : Timestamp
FPostID : BigInt

I have data like above.
I want to update FPostID column with this query

update J_R
set FPostID=select (convert(numeric(20,0), max(FSeqTime) + 0)) from J_R

And I got the same data for each row.

However i expect to get FPostID is same for same FNTrans data.
I want different result if FNtrans is different.

Any body help me?

I'm nothing just gonna being

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-17 : 22:54:53

[code]
UPDATE jr
SET FPostID = jrm.FPostID
FROM J_R jr
INNER JOIN
(
SELECT FNtrans, FPostID = (CONVERT(numeric(20,0), MAX(FSeqTime) + 0))
FROM J_R
GROUP BY FNtrans
) jrm ON jr.FNtrans = jrm.FNtrans
[/code]

KH
[spoiler]Time is always against us[/spoiler]

wawansur
Starting Member

44 Posts

Posted - 2009-09-17 : 23:40:10

Thax Khtan,
Its works

Rgds

I'm nothing just gonna being

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources