March 24, 2004 at 9:57 am
Hello all,
I hope someone will be able to help me out with a problem. I have 2 tables Families(dbo) and RESEARCHethnic(dbo). I want to update Families with some data from RESEARCHethnic. Here’s the table designs:
Famid (PK) int familyid int
Famcarerid int code int
EligStatus int
Consent bit
Refertype int
Firstknown datetime
Famsum1 varchar
Famsum2 varchar
Totalgiven money
Proptype int
Tenuretype int
Family_comp int
Noteligtype int
ethicitytype int
resident bit
resinfo varchar
Im pretty sure we are using SQL server V7.
The column I want to update is ‘ethnicityType’ in families with ‘code’ from RESEARCHethnic. However, families already contains some data which I do not want overwriting/replacing.
I’m a bit of a newbie (I’m a researcher so I just know how to retrieve data) so any help would be greatly appreciated!! If this is straightforward could someone at least point me to an article?
Thanks in advance
Mark
March 24, 2004 at 1:21 pm
You lost me here:
The column I want to update is ‘ethnicityType’ in families with ‘code’ from RESEARCHethnic. However, families already contains some data which I do not want overwriting/replacing.
You are wanting to overwrite some but not all of the ethnicityType for the same family, or all for the same family? If just some what is the criteria for the decision?
March 25, 2004 at 2:20 am
Sorry - I rescued some data off our old XDP database into RESEARCHethnic (for some reason the powers that be didnt want this data at the time but have now decided that they do.). Our users have been entering new data on ethnicity into families which goes into the column 'ethnicityTpe' - I cannot overwrite this data as it refers to new famid's created since our old system was shutdown. What I need to do is add the data in the 'code' column in RESEARCHethnic (rescued from our old system) into the column ethnicityType in families without overwriting the data that our users have already entered.
Each famid is a unique family (we are a charity that helps families with disabled children) I need to update those famid's that have no ethnicityType with an 'code' from 'RESEARCHethnic'.
So a family may exist in families but have no ethnicitytype, if that family exists in RESEARCHethnic it WILL have a 'code' I want that code to slot into the ethnicityType column in the families table. If the family in the families table already has data and also exists in RESEARCHethnic then I do not want the existing data in families overwriting.
I hope this is a little clearer though it sounds damned complicated to me!
As I say I am a complete newbie, I'm a researcher and I tend to use SQL just to retrieve raw data from our database for analysis in SPSS - our DBA has disappeared on maternity leave and the cover hasnt arrived yet.
Many thanks
Mark
March 25, 2004 at 5:27 am
Nope makes a lot more sense now. The only question left is how will you know ethicitytype in the families table has not been set? Will it be NULL or a specific number such as 0? Then when yuo do it will appear something like this.
UPDATE F
SET ethicitytype = R.code
FROM
dbo.FAMILIES F
INNER JOIN
dbo.RESEARCHethnic R
ON
F.FAMID = R.familyid
WHERE
(condition is met such as F.ethicitytype IS NULL or F.ethicitytype = 0)
I suggest testing for safety and even afterwards I would on production backup first, using QA do a BEGIN TRAN, run the UPDATE in the same window, do a select to validate the data looks alright, if looks right do a COMMIT TRAN, if not ROLLBACK TRAN (note do not ehave either COMMIT or ROLLBACK on the screen type in after test as long as the connection window is not closed or lost it will still see the transaction, do test when most people will be out as it will lock the table for most depending on the other queries they are using).
Hope this helps.
March 25, 2004 at 6:04 am
in families the ethnicity type is set at null if data hasnt ben entered so this query
SELECT dbo.Families.famId AS [famid from families], dbo.RESEARCHEthnic.familyid AS [familyid from research ethnic],
dbo.Families.EthnicityType AS [ethnicityType from families], dbo.RESEARCHEthnic.code AS
FROM dbo.Families INNER JOIN
dbo.RESEARCHEthnic ON dbo.Families.famId = dbo.RESEARCHEthnic.familyid
ORDER BY dbo.Families.famId
returns something this:
famid from families familyid from research ethnic ethnicitytype from families code from researchethnic
222222 222222 <NULL> 1
222223 222223 13 7
I would need to update famid 222222 in families with the code 1 from researchethnic but not 222223 because it has a (newer) code in it.
I'll give your code a go on our backup server - I may be a while
Thanks again
Mark
March 26, 2004 at 2:01 am
Works like a treat - Thank You!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply