Solved

Sql update Algorithms

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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

743 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