Problem with update - 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

General SQL Server Forums

New to SQL Server Programming

Problem with update

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2010-09-14 : 14:39:31

I want to update a field by finding the max value and have in increment.

Table name:sysnotes_sql

field I want to update is doc_aware_1.

Sample data:


note_name  name_value note_dt  note_topic doc_aware_1
AR_CUST     00005000   201011    TOPIC       NULL
AR_CUST     00004500   201011    PDA          1
AR_CUST     00003300   201011    PDA          2
AR_CUST     00003349   201011    NOTE#       NULL

What I want to do is anywhere the note_topic = 'PDA' I want to update the doc_aware_1 field. To do that I need to find the max doc number and increment from that.

This is what I've tried but it does not work:


UPDATE SYSNOTES_SQL
SET DOC_AWARE_1 = MAX(DOC_AWARE_1)+1
WHERE NOTE_TOPIC = 'PDA'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-14 : 14:55:27

Do you really need to store the sequencer in the table? Why not do it on the fly with ROW_NUMBER() function?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Vack
Aged Yak Warrior

530 Posts

Posted - 2010-09-14 : 14:59:42

I suppose that would be fine. Apparently I don't know how to use it correctly. this isn't working:

UPDATE SYSNOTES_SQL
SET DOC_AWARE_1 = rownumber()
WHERE NOTE_TOPIC = 'PDA'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources