Query to update table column

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.

ziorinfoAsked:
Who is Participating?
 
pdd1lanConnect With a Mentor Commented:

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
 
Shaun KlineLead Software EngineerCommented:
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
 
LowfatspreadCommented:
@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
 
Shaun KlineLead Software EngineerCommented:
@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
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.