Solved

Query to update table column

Posted on 2011-02-14
4
333 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
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.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

895 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

13 Experts available now in Live!

Get 1:1 Help Now