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.
| Author |
Topic |
|
WJHamel
Aged Yak Warrior
651 Posts |
 Posted - 2011-10-07 : 13:01:08
|
| New issue. I have a table within one database, which is supposed to contain demographic data for persons. There is one row entry per unique person. I have another table, which contains other information about the same people (Same primary key ID #'s), but can contain actual aliases for the person's names (either first, middle, or last).Well, in the first table, where there is supposed to be the primary identifier for each person, some entries were made using their alias as their primary actual name. Now, the primary actual name for that person does reside in the second table. I've done the correct JOIN between these tables to identify such individuals according to the Null value in their name fields, but the match between their unique identifying number. Now i need to pull the missing data from table two and insert such into the relevant blank fields in Table 1. Here is what i have so far for the code for the join:SELECT [From ARR_PER].FNAME AS [FName from ARR_PER], [From ARR_PER].LNAME AS [Lname from ARR_PER], MNI.FNAME AS [FName From MNI], MNI.LNAME AS [LName from MNI], MNI.MNAME AS [MName from MNI], MNI.MNINO AS [MNINO from MNI], MNI.ECSOID AS [ECSOID from MNI], [From ARR_PER].ECSOID AS [ECSOID from ARR_PER] FROM ARREST.dbo.ARR_PER [From ARR_PER] RIGHT OUTER JOIN MNI.dbo.MNI MNI ON ([From ARR_PER].ECSOID = MNI.ECSOID) WHERE (MNI.FNAME = '')The MNI table is the first one, meant to hold the single entry of demographic data for each person. The ARR_PER table contains the aliases for those same persons.Any help is appreciated.thanksJames |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-07 : 13:06:44
|
| can you show some sample data and explain reqd output?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
WJHamel
Aged Yak Warrior
651 Posts |
Posted - 2011-10-07 : 13:14:24
|
| Sorry, i can't put any of the actual data up, but here is as much of an explanation i can give:In the MNI table, the Firstname (FName), Lastname (LName), or Middlename(MName) is missing for some entries. Those entries in that table, however, possess a unique identifying number (MNINO or ECSOID - they are the same number), which identifies the person correctly. In the ARR_Per table (from the "ARREST" database) contains multiple entries per person, with different iterations of their given name. I have identified, via the JOIN i've already done, how those entries in the MNI table which are missing their first or middle names, relate to entries in the ARR_PER table and which of those entries in the latter table contain the missing data needed for the former table. Now that the data has been identified, how do i script the "INSERT" or "UPDATE" to copy the missing data over to the MNI table? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-07 : 13:21:37
|
| why dont you put some sample data? we dont want to see your actual data. just give sample values to illustrate the scenario.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
WJHamel
Aged Yak Warrior
651 Posts |
Posted - 2011-10-07 : 13:35:50
|
| because my browser won't allow me to place a copied image of the data and the formatting of a pasted excel example is a mess when pasted. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-08 : 00:50:10
|
| then post copied image onto shared server and post the link here------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
JOIN, SELECT, and INSERT