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
splogo
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
Tobsey
source
to share
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
Mahmoud Gamal
source
to share