Solved

Query to update table column

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In this article I will describe the Copy Database Wizard 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.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

747 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

18 Experts available now in Live!

Get 1:1 Help Now