Solved

Sql update Algorithms

Posted on 2011-09-19
5
363 Views
Last Modified: 2012-05-12
Hi all
I have a table with the following attributes

see attachemt for question details

Thanks in Advance




P_Id    LastName     Address   validFrom  		Valid until   status
1			Trinity	 26Broad	 01.05.2010		29.06.2010		1
2			Mike	 20Broad	 01.01.2011		29.02.2011		1
3			James	 11Broad	 01.03.2011		29.04.2011		0
4			Phili	 13Broad	 01.05.2011		29.06.2011		0
5			Obama	 16Broad	 01.07.2011		29.08.2011		1
6			Clinton	 11Broad	 01.03.2010		29.04.2010		0
7			Janet	 26Broad	 01.01.2010		29.02.2010		1


How can I update this table based on the validFrom  and Valid until attribute
or I need to sort this table based on validFrom  and Valid until attribute 

Then using the P_Id value as a key and insert in another table Sorted ascending (ValidFrom & Valid until)

P_Id    Name
7		Janet	
6		Clinton
1	    Trinity

Open in new window

question.JPG
0
Comment
Question by:ZURINET
[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
5 Comments
 
LVL 5

Expert Comment

by:25112
ID: 36561172
UPDATE TABLENAME
SET COLUMN_TO_BE_UPDATED = 'UPDATED'
FROM TABLENAME
WHERE validFrom BETWEEN '2010-01-01' AND '2010-03-01'
AND validuntil BETWEEN '2010-04-01' AND '2010-06-01'
0
 
LVL 18

Accepted Solution

by:
ShogunWade earned 400 total points
ID: 36561177
Hmm.

Your question is a little confusing.

To return the table content ordered by validfrom then valid to is this:

SELECT * FROM MyTable ORDER BY validFrom,[Valid until]

If you want to update rows in the table based on a valid from or valiud until value or range then you would use a WHERE caluse in an update statement eg:

UPDATE MyTable
  SET ColumnX='some vlaue'
  WHERE validFrom BETWEEN 20110801 and 20110810  

The last bit is the strange bit.    Inserting data into a table in a specified order does not mean it will be returned in that order when you select from it.   There is only one way to garuntee the order in which data is returned from a table and that is with an ORDER BY clause in the select statement.

There is only 1 case where insertting data into a table in a specific order can be advantageous and that is when you are inserting it in the same order as the clustered index, as it will sometimes provider performance benefits on the insert and often minimisze fragmentation caused by inserts.

If you want it in a different table and want it subsequently ordered then youll neet to ensure that the column you want to order by is in there or a pseudo column such as an ID is present that is seeded based on the order of the validfrom, until columns.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 100 total points
ID: 36561674
>>There is only 1 case where insertting data into a table in a specific order can be advantageous and that is when you are inserting it in the same order as the clustered index<<
Actually not even then.  The only case that is relevant is when the table has an IDENTITY column and it will be used later on in an ORDER BY clause.
0
 

Author Comment

by:ZURINET
ID: 36562106
Dear ShogunWade

Thanks for the input..
You mean to say .. If I do the order by stuff...
I could be able to retrive or sort the tables based on the validity..

The key idea behind the question is that .. for a particular sort .. I need to be able to retrive the values..
based on the validity period..

.ie get the first valid period, the second, the third and so on..
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 36562568
Yes thats correct,  using SELECT * FROM MyTable ORDER BY validFrom,[Valid until]  will return the rows ordered by the valid from column first and then by valid until.

Say for exmaple you wanted the chronoligical order of entries where the address is 26borad (from your example)  

SELECT * FROM MyTable WHERE Address='26Broad' ORDER BY validFrom,[Valid until]
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

630 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