Solved

Sql update Algorithms

Posted on 2011-09-19
5
359 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
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

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 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.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

825 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