How do I delete records from a table using a refrence

[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!

 

[bborissov]

1. Delete the records:

DELETE FROM FavCategory
FROM FavCategory
LEFT JOIN MemberDetails ON 
          FavCategory.MemberId = MemberDetails.MemberId
WHERE MemberDetails.MemberId IS NULL

or

DELETE FROM FavCategory
WHERE FavCateogory.MemberId NOT IN 
                            (SELECT MemberDetails.MemberId
                                    FROM MemberDetails)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[kamazon]

Thanks a lot Borislav! That helped me understand how it works.

 

[kamazon]

hmm here is some more info for you:


I ran your first query with the LEFT JOIN:
DELETE FROM FavCategory
FROM FavCategory LEFT JOIN MemberDetails
ON FavCategory.MemberId = MemberDetails.MemberId
WHERE MemberDetails.MemberId = NULL

gets me "0 rows affected"


DELETE FROM FavCategory
WHERE FavCateogory.MemberId NOT IN
(SELECT MemberDetails.MemberId
FROM MemberDetails)

Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'FavCateogory' does not match with a table name or alias name used in the query.





select memberid, firstname + ' ' + lastname AS memberName
from memberdetails
order by memberid
1 Katie Smith
3 Sandra Jakes
4 Steve Gee
5 John Jones
6 Jenny Jones
7 John Jackson
8 Jack Johnson
9 Seymour Botts





Select * from FavCategory
ORDER BY MemberId
7 4
7 4
7 4
7 6
7 6
7 6
1 10
3 10
6 10
4 11
3 11
5 11
1 12
4 12
6 12
7 12
1 12
4 12
6 12
3 13
3 13
5 13
5 13
2 14
6 14

 

[kamazon]

Thank you sir, I found a typo in the delete from query where category was spelled cateogory, so the 2nd query worked! The first LEFT JOIN query however did not work. If you can see why, I'd love to know. Thanks!

 

[bborissov]

Not:

WHERE MemberDetails.MemberId = NULL

It must be:

WHERE MemberDetails.MemberId [b][COLOR=red]IS[/color][/b] NULL

Nothing can be equal or different to NULL.
Think of NULL as SQL Server "I DON'T KNOW"
So
"I DON'T KNOW" = "I DON'T KNOW" is?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[kamazon]

Thanks again Mr. Borissov