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

LVL 19
erikTsomikSystem Architect, CF programmer Asked:
Who is Participating?
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
Bro try this..

--


;With cte As
(
	SELECT folioNo,pgPos,
	ROW_NUMBER() OVER (ORDER BY folioNo ) AS RN
	FROM  tbl_160154dd
)
UPDATE x
SET x.pgPos = CASE WHEN RN % 2 = 0 THEN 'R' ELSE 'L' END 
FROM cte x


--

Open in new window


--
0
 
Pawan KumarDatabase ExpertCommented:
@Author - Do you need an update query or Select Query?
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0
 
erikTsomikSystem Architect, CF programmer Author Commented:
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

0
 
PortletPaulfreelancerCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.