Author Archives: chrismay

Moving changes from one table to another

Posted on by

You just screwed up the database, and now you need to make some surgical repairs to the database.

Or maybe you have data in development and production tables and you want to move some data changes from one to the other.

Basically the problem is that you have 2 tables with the same schema where 1 column has some different values in one table when compared with the other.

You can’t do an update on an inner join statement, but you can create an updatable view containing the fields you need and then run an update statement on it.

A view is updatable if it meets the following conditions:

A view is updatable if:

  • select_statement has no aggregate functions in the select list and does not contain the TOP, GROUP BY, UNION, or DISTINCT clauses. Aggregate functions can be used in a in a subquery in the FROM clause as long as the values returned by the functions are not modified. For more information, see Aggregate Functions.
  • select_statement has no derived columns in the select list. Derived columns are result set columns formed by anything other than a simple column expression, such as using functions or addition or subtraction operators.
  • The FROM clause in the select_statement references at least one table. select_statement must have more than nontabular expressions, which are expressions that are not derived from a table. For example, this view is not updatable: 
CREATE VIEW NoTable AS
 SELECT GETDATE() AS CurrentDate,
 @@LANGUAGE AS CurrentLanguage,
 CURRENT_USER AS CurrentUser

Whole Genome Shotgun Sequencing

Posted on by

This is the paper I wrote for my Bioinformatics course at the University Of Chicago, discussing some of the methods available to sequencing DNA of complex organisms, and how Myers was able to use the whole genome shotgun method to sequence the human genome.

TermPaper.pdf (793.88 KB)

 

Getting Just the Date Out Of GETDATE

Posted on by

SQL Servers’ GETDATE() function will return the current DateTime.  But what if you just want the current date.

CAST(FLOOR(CAST(GETDATE() AS float)) AS datetime)

or

CAST(CONVERT(VARCHAR(10), GETDATE(), 111) AS DATETIME)

See this page for even more options.

Or, if you want to get just the current time out of GETDATE or any datetime for that matter, check out this page (it is one of the FAQs).

 

Whole Genome Shotgun Sequencing.

Posted on by

I am writing a paper for where I am supposed to defend the claim that Whole Genome Shotgun Sequencing was successful at mapping the human genome.

In the research I have done, I have come across a bunch of papers from the late 90s from people on both sides (e.g. Venter/Myers PRO, Green
AGAINST).

It seem then after the release of the human genome, there again a number of papers looking at the results, kind of doing a post mortem on the whole debate.

However, these articles seem to indicate that WGS would NOT have worked in the human genome, where such a large portion of the genome is a duplicate, if not for the IHGP teams work.  Some papers seemed to suggest that this hybrid method would be the standard way to sequence genes.

I have not found much on the subject from recent months.  Almost all
my references are from 2002 or earlier.

I tried posting to some newsgroups for some opinions and got back nada.

I must not be looking in the right spots, but it sure is frustrating.

UPDATE: After more research, it appears that the reason I was finding so many articles complaining about the WGS method was due to the massive egos that were seeing their Nobel prize dreams going up in smoke.  Venter and Myers successfully used WGS on the mouse genome, and then later applied it again to human, without any data from GenBank, validating the results, and the technique.

Functions To Get DNA and RNA Complementary Codes

Posted on by

For my most recent project at U of C, I had to write some script to find places where DNA and RNA might bind.

DNA (and RNA) will bind when 2 strands have a complementary sequence.  A binds with T, T bind with A, G binds with C and C binds with G. 

Basically the same thing happens with RNA.

Here are 2 functions that will quickly find the complementary sequence for DNA and RNA:

Public Function FlipRnaCode(ByVal sEnd As String) As String
    sEnd = Replace(sEnd, "U", "a", 1, -1, CompareMethod.Binary)
    sEnd = Replace(sEnd, "A", "u", 1, -1, CompareMethod.Binary)
    sEnd = Replace(sEnd, "G", "c", 1, -1, CompareMethod.Binary)
    sEnd = Replace(sEnd, "C", "g", 1, -1, CompareMethod.Binary)
    sEnd = UCase(sEnd)
    Return sEnd
End Function

Public Function FlipDnaCode(ByVal sEnd As String) As String
    sEnd = Replace(sEnd, "T", "a", 1, -1, CompareMethod.Binary)
    sEnd = Replace(sEnd, "A", "t", 1, -1, CompareMethod.Binary)
    sEnd = Replace(sEnd, "G", "c", 1, -1, CompareMethod.Binary)
    sEnd = Replace(sEnd, "C", "g", 1, -1, CompareMethod.Binary)
    sEnd = UCase(sEnd)
    Return sEnd
End Function