1

I typically work with Oracle, and a query like this would be very easy. I have googled my brains out. Basically it is to get the max identifier of the prior records of comments.

In Oracle I would have done an update like this

UPDATE NOTES N1
SET MASTER_RECORD_NUMBER = 
(SELECT MAX(MASTER_RECORD_NUMBER)
 FROM NOTES N2
 WHERE N1.CUSTOMER_NO = N2.CUSTOMER_NO
 AND N2.MASTER_RECORD_NUMBER < N1.MASTER_RECORD_NUMBER
 AND N2.CODE IS NOT NULL)
WHERE N1.CODE IS NULL;

Basically the record numbers are sequential, and the code is not present if it is a continuation. The query is a little longer than this, involving dates and such, but this is generally what I am trying to do.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Marc Perry
  • 13
  • 2
  • http://stackoverflow.com/questions/2334712/update-from-select-using-sql-server pay attention to how table_A is an alias of "Some Table" in the example. I'm thinking notes N1 vs N2 is the problem – xQbert Aug 01 '16 at 18:40

1 Answers1

0

Try this..

UPDATE  N1
SET MASTER_RECORD_NUMBER = 
(SELECT MAX(MASTER_RECORD_NUMBER)
 FROM NOTES N2
 WHERE N1.CUSTOMER_NO = N2.CUSTOMER_NO
 AND N2.MASTER_RECORD_NUMBER < N1.MASTER_RECORD_NUMBER
 AND N2.CODE IS NOT NULL)
 from notes n1
WHERE N1.CODE IS NULL;
TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
  • 1
    Thank you. This worked for me. Can't believe it is so similar, but was causing me such a fuss. – Marc Perry Aug 01 '16 at 20:27
  • @MarcPerry:Glad it helped,question was little unclear,please read this link to get amazing fast answers..https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ – TheGameiswar Aug 02 '16 at 06:36