Re: Updating linked tables
From: Wayne Snyder (wayne.nospam.snyder_at_mariner-usa.com)
Date: 08/11/04
- Next message: Wayne Snyder: "Re: Number of IOs Per Second?"
- Previous message: Fibre Optic: "Re: Shrink DB"
- In reply to: NoelH: "Updating linked tables"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 11 Aug 2004 08:16:46 -0400
An outer join is the way to go...
I would an FK constraint on the second table as well, so no one deletes the
parent, leaving an orphan child.
Regarding the update, I would test ...
some people check for existence then do the appropriate thing Insert or
update
others simply attempt the update, and on failure do an insert,
But I don't think anyone would recommend the delete, insert option...
-- Wayne Snyder, MCDBA, SQL Server MVP Mariner, Charlotte, NC www.mariner-usa.com (Please respond only to the newsgroups.) I support the Professional Association of SQL Server (PASS) and it's community of SQL Server professionals. www.sqlpass.org "NoelH" <NoelH@discussions.microsoft.com> wrote in message news:1B17DE38-0C74-4D6A-84D3-B2F8501D2A01@microsoft.com... > > I have a users table that stores basic information for all users. Then I > created a second tables like tbl_UsersMarried to store the extra information > that only married users have. Only 40% of the users are married. I'm just > using married as an example. > > 1) I have a few tables like tbl_UsersMarried. I can create a view that outer > joins those table so I can select all users and all of their information. Is > there a more efficient way to do this than using outer joins? > > 3) Every time a user updates there Married information I need to check if a > record in tbl_UsersMarried already exists for that user so I know if I should > Update the Record or Insert a new one. Would it be better to do a delete on > tbl_UsersMarried for that user whether they have a record or not and just do > an insert? > > > CREATE TABLE tbl_Users ( > UserID INT IDENTITY (1, 1) NOT NULL PRIMARY KEY , > FirstName VARCHAR (20) NOT NULL , > LastName VARCHAR (20) NOT NULL , > Email VARCHAR (50) NOT NULL , > ... ); > > CREATE TABLE tbl_UsersMarried ( > UserID INT NOT NULL PRIMARY KEY , > DateMarried DATETIME NOT NULL , > State VARCHAR (2) NOT NULL , > ... ); > > > Thanks in advance for any advise you can give me. >
- Next message: Wayne Snyder: "Re: Number of IOs Per Second?"
- Previous message: Fibre Optic: "Re: Shrink DB"
- In reply to: NoelH: "Updating linked tables"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
