Update specific rows in sql table

Question

I have the following SELECT statement.

SELECT     AETID, DSRID, FYEID, BASICRATE, EURORATE, 
  COMID, BRAID, CURID, CURIDCONV, ENTRYDATE, EVNUMBER, 
  ORIGIN, IEEID, RELDOCNUM, GJOID, JOURNALNUM,
  JUSTIFICATION, KEPYOAMOUNT, KEPYONUM, STATUS, 
  UPDSTATUS, TOTALCB, IDLINKED, CREUSERID, CREUSERDATE, 
  UPDUSERID, UPDUSERDATE, ACEIDCROSSENTRY, ACEIDCROSSEDENTRY, 
  ISCROSS, ISCROSSED, ACEIDCANCEL, ACEIDPRIMARY, CCEID
FROM         EliteUser.ACE
WHERE     (JOURNALNUM > 1193) 
  AND (JOURNALNUM < 2387)

What I want to do is update the specific rows so that where journalnum = 1194 updates to 1, where journalnum = 1195 updates to 2, etc

Solution 1

Which column is the new value to go in? Assuming you want to update the JournalNum column so that it starts at 1:

UPDATE
    EliteUser.ACE
SET
    JournalNum = JournalNum - 1193
WHERE
    JournalNum > 1193
AND JournalNum < 2387

Solution 2

UPDATE EliteUser.ACE
SET journalnum = journalnum - 1193
WHERE  JOURNALNUM > 1194 
   AND JOURNALNUM < 2387;

Or:

UPDATE e
SET e.JOURNALNUM = t.rownum
FROM EliteUser.ACE e
INNER JOIN
(
  SELECT JOURNALNUM, ROW_NUMBER() OVER(ORDER BY JOURNALNUM ASC) rownum
  FROM EliteUser.ACE
  WHERE  JOURNALNUM > 1194 
    AND JOURNALNUM < 2387
) t ON e.JOURNALNUM = t.JOURNALNUM