Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 366
  • Last Modified:

Sql update Algorithms

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
ZURINET
Asked:
ZURINET
2 Solutions
 
25112Commented:
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
 
ShogunWadeCommented:
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
 
Anthony PerkinsCommented:
>>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
 
ZURINETAuthor Commented:
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
 
ShogunWadeCommented:
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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now