How to delete all the data of a specific username?

Question

0.00/5 (No votes)

See more:

I want to delete all the data of a UserName from all the table from database.
Actually I have UserName used in all the tables as FK.
Now i want to delete a specific User from my database.

What I have tried:

DELETE s.UserName, p.Name,p.Father_Name,s.Email,p.DOB,p.Gender,p.Nationality,p.Domicile,p.CNIC,p.Mobile,p.Address, e.SSC_OM,E.SSC_TM,E.SSC_EB,e.HSSC_OM,e.HSSC_TM,e.HSSC_EB, d.Choices_1,d.Choices_2,d.Choices_3
FROM Signup s
INNER JOIN Pers_D p ON p.UserName = s.UserName
INNER JOIN Edu_D e on e.UserName = p.UserName
INNER JOIN Dep_S d on d.UserName = e.UserName
WHERE [d].UserName LIKE '%Abuzar%'

Posted 21-Jul-16 1:02am

Stack Holder

Updated 24-Jul-16 23:34pm

v2

Add a Solution

Comments

[no name] 21-Jul-16 7:09am

One way would be to modify the foreign key constraints by including ON DELETE CASCADE

Stack Holder 21-Jul-16 7:11am

what?

Stack Holder 21-Jul-16 7:15am

in sql server 2012!

[no name] 21-Jul-16 7:18am

Yes that is also possible in SQL Server 2012! :)

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2016-07-21T01:20:00

Solution 1

What 0x01AA meant was that when you establish the Foreign Key relationship between the Signup table UserName column and the Pers_D, Edu_D, and Dep_s Username entries you can add a Constraint ON DELETE CASCADE: SQL Server: Foreign Keys with cascade delete[^]
What happens then is that when you delete the row from the master table - Signup - the system will automatically remove all teh rows from dependant tables for you:

SQL

DELETE FROM Signup WHERE UserName = @UserName

Will do it automatically for you.

And don't use LIKE with DELETE! (Or even UPDATE - keep LIKE for SELECT where possible) it's very dangerous.
Suppose you want to DELETE user "old" and you say:

SQL

DELETE FROM Signup WHERE UserName LIKE '%old%'

The user "old" will go - but so will "bold", "oldsmobile" and ... "Stack Holder".
LIKE is dangerous - be very careful where you use it!

I would also suggest that using the UserName as a key field is a bad idea - use an ID value instead and it saves space, saves time, and allow the user to change his name without you having to update all related records.

Posted 21-Jul-16 1:20am

OriginalGriff

Updated 21-Jul-16 3:48am

v2

Comments

F-ES Sitecore 21-Jul-16 8:13am

"it's very dangerous"

Not as dangerous as cascading deletes :)

OriginalGriff 21-Jul-16 8:25am

Cascading deletes are dangerous, yes - but not half as bad as cascading deletes with LIKE! :laugh:

Stack Holder 21-Jul-16 9:33am

with
DELETE * FROM Signup WHERE UserName = @Abuzar

error cames
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '*'.

OriginalGriff 21-Jul-16 9:48am

Sorry, wrong database! Just remove the "*".

kaushik ahir 25-Jul-16 5:38am

this is not select query it is Delete query so please follow synatax: delete from table name where unm=@unm

Stack Holder 21-Jul-16 9:54am

then error is:

Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK_Pers_D". The conflict occurred in database "Online_Admission_Systemz", table "dbo.Pers_D", column 'UserName'.

Stack Holder 21-Jul-16 9:58am

I am working on a small project in University and we have no more time as we have to submit it, within few days. I'm in tension, please reply me fast.

OriginalGriff 21-Jul-16 10:05am

Then think about what you are trying to do instead of just blindly following instructions!
Of course I don't know which table of yours holds the master reference and which one you set as foreign keys. So use the master table instead of Signup...

Stack Holder 21-Jul-16 10:19am

Signup is a master table (means UserName is it's PK) and in all other tables UserName is FK.

OriginalGriff 21-Jul-16 10:46am

No, Primary Key just tells SQL which column you are going to access the table via most, so it should optimise indexes for that column. Every table should have a PK.
Whichever table is the master is the one to which all the other foreign keys refer.

Stack Holder 21-Jul-16 10:49am

unsolved but thanks your effort.

kaushik ahir · Answer 2 · 2016-07-24T23:34:00

Solution 2

delete from Signup where UserName=@UserName

Posted 24-Jul-16 23:34pm

kaushik ahir