Query Help

[sk8ology]

I'm trying to update a table and clear out all internal notes made by employees because clients are able to see them on our website. All notes start with #Blind Note: and are followed by the notes on the next row and proceed in order with the seq_no just like my example below. The DNT_DBR_NO is the unique identifier. I want to clear out all DNT_NOTE's and replace it with 'Manager Auditing File'.

DNT_SEQ_NO DNT_NOTE DNT_DBR_NO
115 #Blind Note: 177187
115 Sample notes 177187
115 Sample notes 177187
139 #Blind Note: Sample notes 177190
139 Sample notes 177190
139 Sample notes 177190
222 #Blind Note: 224510
222 Sample notes 224510
222 Sample notes 224510

I know I'm off but this is what I could think of.

Update tabe
set DNT_NOTE = 'Manager auditing file'
Where DNT_SEQ_NO in
(select DNT_SEQ_NO from table where DNT_NOTE like ('#Blind Note:%')

Any help?

 

[bborissov]

UPDATE TableName SET DNT_NOTE = 'Manager auditing file'
FROM TableName
INNER JOIN (SELECT DNT_SEQ_NO
                   FROM TableName
            WHERE DNT_NOTE LIKE '#Blind Note:%') Tbl1
      ON TableName.DNT_SEQ_NO = Tbl1.DNT_SEQ_NO

Not tested!!!!

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.