help with SQL update query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

help with SQL update query

Author

Topic

lazyme
Starting Member

8 Posts

Posted - 2010-09-27 : 13:09:55

Hi, I have 2 sql student tables Student1 and Student2


Student1
Student ID   Name    Test taken     Test Date      Test Result
1            Sam     TB             05/23/2010     Pending
1            Sam     MMR            06/20/2010     Pending
2            Tom     MMR            07/14/2010     Pending

Student2
Student ID   Name    Test taken     Test Date      Test Result
1            Sam     TB             05/23/2010     Pass
2            Tom     MMR            07/14/2010     Pass

As you can see, both the tables have no primary key and some entries in Student1 are updated in Student2

I want to write an sql query that will update all the matching Student1 entries from Student1. For the entries to match, the Student ID, Name, Test taken and Test Date fields should match. Then for these matching entries we check if the Test Result has been updated and update the same in the table Student1.

So, our Student1 table after executing the query should be


Student1
Student ID   Name    Test taken     Test Date      Test Result
1            Sam     TB             05/23/2010     Pass
1            Sam     MMR            06/20/2010     Pending
2            Tom     MMR            07/14/2010     Pass

Someone please help me write this query.

craigwg
Posting Yak Master

154 Posts

Posted - 2010-09-27 : 14:42:38

I would let table 2 stand as it is now. Table 1 should be a view. You can build the view to select the most recent data only based on your composite primary key. That way you only ever have to update one table by adding new scores as they come in. Does that make sense?

Craig Greenwood

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources