Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Sql update Algorithms

Posted on 2011-09-19
5
Medium Priority
?
364 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 1600 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 400 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
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 date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

721 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