Solved

Query to update table column

Posted on 2011-02-14
4
334 Views
Last Modified: 2012-06-22
I have a table which has one column called OrderBy.

by this column I sort the table record before showing them to the user.

User can change the order of records and then this column gets updated.

Now my client has told me to change the way user can reorder the records

He wants to simply puts a textbox and what ever value user enters in it should put the record at that position.

I am trying to write one common SP so that I can update the ORderby column by the value which user enters in the textbox.

Along with the updation of Orderby column I also need to keep the orderby column correct
so that no two records can have same orderby value.

0
Comment
Question by:ziorinfo
  • 2
4 Comments
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 34887515
If you are looking for an update statement that will only update if the new value does not already exist then:

Update <table>
Set OrderBy = <New value>
WHERE Not Exists (select 1 from <table> where OrderBy = <New Value>)
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34887754
@shaun   logically that's not going to work too well....

ziorinfo are you sure that client has decreed that the order by value cannot be duplicated?
  as it shouldn't make any real difference  to them....

 why shouldn't they be able to label a block of rows as 10 and another set as 20  etc....

unless you mean they want to actually specify a physical number for the display
in  which case you'd need to adjust all the other rows in the set as well.....

what does the table actually look like....
and what selection criteria do they use to display the rows....

does this sortorder have any permenant meaning or is it just for this "one-off" display?

more background please



0
 
LVL 8

Accepted Solution

by:
pdd1lan earned 125 total points
ID: 34888080

DECLARE  @orderbyCol AS VARCHAR(60)

SET  @orderbyCol ='value from textbox'

IF EXISTS
( SELECT 1 FROM TABLE WHERE ORDERBY=@orderbyCol)

SELECT * FROM TABLE ORDER BY @orderbyCol

ELSE
    UPDATE TABLE
    SET ORDERBY = @orderbyCol
 
     SELECT * FROM TABLE ORDER BY @orderbyCol
0
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 34888699
@Lowfat -- True. The missing piece is either the PK for the row or the original orderby value. So...

Update <table>
Set OrderBy = <New value>
WHERE OrderBy = <Old value> and Not Exists (select 1 from <table> where OrderBy = <New Value>)

Or...

Update <table>
Set OrderBy = <New value>
WHERE <PK field> = <PK ID>
    and Not Exists (select 1 from <table> where OrderBy = <New Value>)
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
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.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

785 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