[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Query to update table column

Posted on 2011-02-14
4
Medium Priority
?
361 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 27

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 500 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 27

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
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 video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

656 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