Link to home
Start Free TrialLog in
Avatar of erikTsomik
erikTsomikFlag for United States of America

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

Open in new window


With cte As
(
SELECT folioNo,pgPos,
ROW_NUMBER() OVER (ORDER BY folioNo ) AS RN
FROM  tbl_160154dd
)

SELECT * FROM cte

Open in new window

Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

@Author - Do you need an update query or Select Query?
ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Jim Horn
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.
Avatar of erikTsomik

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	

Open in new window

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.