Updating a column

Question

Updating a column

mark woolley

SSC Enthusiast

Points: 154
More actions
March 24, 2004 at 9:57 am

#84053

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:

Families (135,000 rows)                 RESEARCHethnic (58,000)

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

http://www.familyfund.org.uk

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

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?

mark woolley SSC Enthusiast Points: 154 More actions · Answer 2

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

Antares686 SSC Guru Points: 125444 More actions · Answer 3

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.

mark woolley SSC Enthusiast Points: 154 More actions · Answer 4

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

mark woolley SSC Enthusiast Points: 154 More actions · Answer 5

mark woolley

SSC Enthusiast

Points: 154

March 26, 2004 at 2:01 am

#500593

Works like a treat - Thank You!

Updating a column

Families (135,000 rows) RESEARCHethnic (58,000)