Removing records from 1 MS-SQL Table that don't match another Table

[KAMAZON]

I have a "Favourite Category" Table w/ a MemberID (it had a foreign key relationship to the MemberDetails MemberId field but i deleted it a few mins ago) and a MemberDetails Table with a Memberid Primary Key. I updated the Favourite Category table with MemberIds that do not exist in the MemberDetaisl table yet. I decided to remove those records, then re-add them only after creating the member Ids so I guess my question is 2 parts:

1.) How do I remove those records? This is the best query I could fathom which I'm sure has a million mistakes. Hell, the return is
"Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'TABLE'." So much for that.

UPDATE TABLE FavCategory
DELETE ColumnId, MemberId
FROM FavCategory.MemberId = MemberDetails.MemberId
WHERE FavCategory.MemberId NOT IN MemberDetails.MemberId


Question 2 would be how to create a CHECK constraint so that I don't make these noob mistakes next time. Thanx!

 

[Argo]

The query to delete missing child records is: (this would actually work in all flavors of SQL servers).

delete from FavCategory fc where not exists (select 1 from MemberDetails md where md.meber_id=fc.meber_id).


Note that "not exists" is an order of magnitude more efficient that "not in", at least under Oracle. Not sure how to setup check constrants under MsSQL.

 

[KLin]

all of your SQL questions so far sound like homework questions. Shouldn't you be researching this yourself?

 

[KAMAZON]

I am not in any class to be assigned any homework, it would be nice if they were in fact homework since there would be classmates and a teacher. Besides, what question doesn't sound like homework unless you specify that you are having a problem in a production environment? I do research these myself for quite a while before bothering to ask Anandtech so I can learn, and have found lots of solutions personally. However, seeing that I've been learning SQL on my own for over month and have asked at most, 6 questions from Anandtech on the topic of SQL, I think I'm being very reasonable.


The query to fix it was:
DELETE FROM FavCategory
WHERE FavCateogory.MemberId NOT IN
(SELECT MemberDetails.MemberId
FROM MemberDetails)

 

[Evadman]

Originally posted by: KAMAZON
Question 2 would be how to create a CHECK constraint so that I don't make these noob mistakes next time. Thanx!

You want a foriegn key. your table must be converted to InnoDB from MyISAM if it hasn't been already.

ALTER TABLE tablename TYPE=InnoDB

add FK:

alter table FavCategory add Foreign key (MemberId) references MemberDetails(MemberId)