Clever Geek Handbook

Updating specific rows in sql table

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)

I want to update certain lines so that it journalnum = 1194

updates to 1, where it journalnum = 1195

updates to 2, etc.

+3


source to share


2 answers


What column is the new value in? Assuming you want to update the JournalNum column to start at 1:



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


source 


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
+2


source






More articles:


All Articles