Solved

sql help

Posted on 2016-09-20
5
48 Views
Last Modified: 2016-09-23
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

0
Comment
Question by:erikTsomik
5 Comments
 
LVL 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
@Author - Do you need an update query or Select Query?
0
 
LVL 16

Accepted Solution

by:
Pawan Kumar Khowal earned 500 total points
Comment Utility
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
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
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
 
LVL 19

Author Comment

by:erikTsomik
Comment Utility
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
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now