update field on a select query

• lee.hopkins

  Old Hand

  Points: 366

  More actions

  August 28, 2024 at 12:42 pm

  #4450934

  I have a sql query that runs on demand to generate a report. I want to update a field REPORTSENT to a Y on the selected results.

  So the next time the report is requested only the Records with a REPORTSENT = N will be returned. Normally would run the query for the records and then an update with the same where clause to mark the REPORTSENT = Y. I was hoping that there would be a way to do it in one process.

• drew.allen

  SSC Guru

  Points: 77011

  More actions

  August 28, 2024 at 1:31 pm

  #4450962

  Do it in the other order.  Use UPDATE to update the records using the OUTPUT clause to output your required fields.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• Phil Parkin

  SSC Guru

  Points: 247154

  More actions

  August 28, 2024 at 2:17 pm

  #4450973

  Drew's suggestion is the way forward. Here is an article which demonstrates the technique:

  https://www.sqlservercentral.com/articles/the-output-clause-for-update-statements

  ---

• lee.hopkins

  Old Hand

  Points: 366

  More actions

  August 28, 2024 at 2:31 pm

  #4450982

  Thank you for pointing me to a solution.

• September 3, 2024 at 6:39 am

  This was removed by the editor as SPAM