erikTsomik
asked on
sql help
I am trying to update the table. where I have following columns:
folioNo,pgPos,PgName,
folioNo is the order
pgPos is either right or left (if folioNo is odd then put L (left) if even put R(right)) . Let say I want to add another FolioNo in beteween 11 and 12. I will need to make new itemWith a folio 12 and PgPos R. and the following reordered accordingly with update in folioNo and PgPos
sample data
folioNo,pgPos,PgName,
folioNo is the order
pgPos is either right or left (if folioNo is odd then put L (left) if even put R(right)) . Let say I want to add another FolioNo in beteween 11 and 12. I will need to make new itemWith a folio 12 and PgPos R. and the following reordered accordingly with update in folioNo and PgPos
sample data
PDF_Filename FolioNo PgPos
A-1 11 R
A-2 12 L
A-3 13 R
A-4 14 L
A-5 15 R
A-6 16 L
With cte As
(
SELECT folioNo,pgPos,
ROW_NUMBER() OVER (ORDER BY folioNo ) AS RN
FROM tbl_160154dd
)
SELECT * FROM cte
@Author - Do you need an update query or Select Query?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
It would help if you can give us a data mockup of your desired return set, as it's not clear from the description. Thanks.
ASKER
I would expect something like this
PDF_Filename FolioNo PgPos
A-1 11 R
A-5 12 L I added this record
A-2 13 R
A-3 14 L
A-4 15 R
A-5 16 L
A-6 17 R
Consider using a "computed column" instead
With this approach as soon as you insert a new row it will calculate the L or R value for you without needing an update.
ALTER TABLE tbl_160154dd
ADD [ComputedColumn] AS CASE WHEN RN % 2 = 0 THEN 'R' ELSE 'L' END
If you want to continue using [pgPos] as the column name you would need to drop that column first, then add it with the calculation
Note: It is possible to "persist" (store) the calculation, refer to the URL above for more details on this.
With this approach as soon as you insert a new row it will calculate the L or R value for you without needing an update.
ALTER TABLE tbl_160154dd
ADD [ComputedColumn] AS CASE WHEN RN % 2 = 0 THEN 'R' ELSE 'L' END
If you want to continue using [pgPos] as the column name you would need to drop that column first, then add it with the calculation
Note: It is possible to "persist" (store) the calculation, refer to the URL above for more details on this.