CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2008
Mohammad Asgari
Mohammad Asgari

Reputation: 663

How do i delete a record from 2 table which have relationship?

As in Image , I want to Delete a record or row from the Profile and Login tables Where the Profile table have relationship to Login By Id in the Profile table and Profileid in the Login table. enter image description here I use this query :

DELETE      Profile.Id , Profile.Lastname, Profile.Name, Profile.Phone, Profile.Countryid,      Profile.Email, Profile.[Address], Profile.Typeid, Profile.[Status], Profile.Regdate, 
                     Login.[Password] Login.Username, Login.Id AS Loginid, Login.Profileid
FROM            Login INNER JOIN Profile ON Login.Profileid = Profile.Id

WHERE        Profile.Id = 4

But when i run this query i get this error Microsoft Sql Server 2008:

Msg 102, Level 15, State 1, Line 1 Incorrect syntax near ','.

Is that appropriate query to do this Delete job? if not How do i do that? I mean How query should be?

Upvotes: 1

Views: 1916

Answers (3)

BizApps
BizApps

Reputation: 6130

Try this:

DELETE FROM Login  WHERE Profile.Id = 4  --fist execute delete from child with FK

DELETE FROM Profile WHERE Profile.Id = 4    -- then execute delete from parent PK

First delete records from your Login Table which contain [FK]Profile.Id = 4.

Then If there is no [FK]Profile.Id = 4 exist on Login Table then you can now delete it on Profile Table [PK]Profile.Id = 4.

Regards

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 138980

You should have a look at the syntax for delete.

You also need two delete statements. One for each table.

delete from login where profileid = 4

delete from profile where id = 4

Upvotes: 1

Zohaib
Zohaib

Reputation: 7116

why not do something like this,

delete from profile where profile_id = 4

delete from login where profile_id = 4

Although you might wanna have cascading options set, for your foreign keys. That way you will only have to delete entry from profile table, and all the rows will from other tables will be deleted automatically where profile id =4. (or whatever id you delete from profile table.)

Upvotes: 1

Related Questions