Sql query to delete from multiple tables
Vložit
- čas přidán 19. 03. 2021
- Sql query to delete parent child rows | cascade delete in sql server | same foreign key in multiple tables | delete from multiple tables sql server | how to enable cascade delete in sql server | foreign key constraint cascade delete | sql cascade delete example
Notes and Slides
csharp-video-tutorials.blogsp...
SQL Server Tutorial for beginners
• SQL Server tutorial fo...
SQL Server Interview Questions and Answers
• SQL Server Interview Q...
SQL Server Performance Tuning and Query Optimization
• SQL Server Performance... - Věda a technologie
Best quick intro to references, referential integrity and cascading deletes for SQL beginners I've seen so far
Thank you very much, spent ages on the internet trying to find how to do this until I found your video on CZcams, it was a lifesaver!
Very clear explanation with real case scenarios.
Thanks Sir, You always give perfect explanation.
Sir in this year's u had thought us sql big thanx for that, I have one request sir please release videos of no-sql for a change take it to next level.
Your teaching is awesomely understanding.
Thank you Venkat ! Good stuff as always !
Golden info for newbie like me. Marvelous
Thank you sir. The way you tech and the way you explain the topic is awesome it's directly hit into the brain. Once again thanks sir 👏👏👏👏👏👏👏👏👏👏👏👏👏
Excellent video. Thanks a lot.
I used to ask this question in all my interviews. The explanation is good. 🙂
Thank you Nandhakumar. It would be great, if you can share what you look for in an interviewee . Could be helping many trying to find a job as a software engineer. Thank you for sharing your valuable experience.
@@Csharp-video-tutorialsBlogspot Sure Venkat. Can we connect through email? I can send some over email.
Absolutely Nandhakumar. You can reach me on kudvenkat@gmail.com. Thank you for your time .
@@nandhukumar86 Can you forward that email to me as well, it helps me in preparing for interviews.. srharsha299@gmail.com
Best ever tutor at CZcams please share all in one video on asp.net n c#
Great explanation and examples
Best channel, best vedios on every topic that i learned. Going to complete all videos and practice them😍
good explanation.. thanks sir..
You could also make a before delete trigger using instead-of trigger on the parent table which deletes the child rows first.
Absolutely Jan, another way of doing. Thank you for sharing your knowledge.
thank you so much for such nice explanation. Finding it very usefull
You are very welcome and thank you very much for the feedback Abhiram. Means a lot. The following is the link our complete SQL Server tutorial. Hope you will find it useful.
czcams.com/play/PL08903FB7ACA1C2FB.html
Great Video! Can I request you to make video on following features of SSMS 1) SQL Jobs 2) Profiler vs xevent profiler 3)Database Tuning Adviser
Thanks 😊
Awesome content. Keep posting content so that we can continue lerning :)
Thank you!
As Many people asking please make more videos on SQL
Hello Jack - Absolutely, please let me know if you have a specific question or a concept in mind that you want me to cover. All the very best with everything you are doing.
Hi Venkat, I'd second this. Your SQL videos have been invaluable. I always direct others to them if there's a concept they need to learn.
The main thing I was missing is more detail on the DBA side of things. Particularly difficult to find good information on design considerations for indexes - all the different types, when and why to use one over another, how many indexes to add to a table, how many columns to include in a single index, how to effectively test them, managing them in ETLs, maintenance etc.
@@Csharp-video-tutorialsBlogspotHi Venkat, As you have covered most I see some topics on Normalisation like BCNF, 4F, 5F are left.
This is just an example so if you can cover any leftover from the current list it helps a lot
Please do video's on SSIS, SSAS nd SSRS in detail.
Please do videos on ssis Ssrs ssas
6:20 This is also a really dangerous query as it only tells you the affected rows in the Gender tables and don't tell you anything about the other 4 rows deleted from the Students and Teachers table.
700 like from me and a sub, im starting to watch all your vids, gg for work 😊
I watch all your videos.. great content. Can you please make Azure Synapse Analytics series ?
Please make a video on Grouping sets and Roll Up function with multiple tables joined!
Great video appreciate it!👍
Hello Sharbashis - Grouping sets and roll up is discussed in our SQL tutorial for beginners course. Please check out videos from Part 101 to 105.
csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html
Thanks for sharing
Thank you sir,,
Big thanks to you sir, miku possible aithe ee course ni Telugu lo upload cheyandi sir because non IT students,and Telugu medium students kosam videos cheyandi sir,vallaki enka easy ga avuthundhi sir.
Thanks Bro.
Thank you very much sir for this excellent tutorial!
Excellent..
Hi Venkat, how about disabling the FK constraint(s); deleting the rows; and enabling the constraint(s)? Please advise.
Great info. Thank you for this
Thanks sir
Thanks
Hi Sir, I love your content. Also, please make videos on SSIS.
Please like his comment if u also want separate video on SSIS.
Based on your example is cascade delete faster if you are using FK? I am thinking it would not have to recheck the FK constraints again on the parent delete
I am big fan sir
Using a function in where condition can impact query performance. If you are playing with big data
Hi Venkat,
Can you please do a video on parameter sniffing and what is the best approach to solve parameter sniffing problem?
Kudvenkat please post more sql related videos your sql server videos is awesome.
Nice video
Hi Kudvenkat, I am a new to SQL SERVER and recently the msdb database is in suspect recovery mode and all the databases are in recovery pending and SQL AGENT is not visible on the instance.My challenge is I can’t solve the problem as the file path after the sql server upgrade from sql 2016 to sql2019 is not recoverable as I tried to rebuild the msdb database.I moved the msdb and renamed it to make it easier to rebuild the msdb but to no avail.Please if you can do a video on msdb suspect recovery mode.Thank you! Keep it up you already helping plenty of people like myself.
Thank you in advance.
Many thanks sir, but can we do same action with access database? Or do you have any advice?
Thanks and regards
Nice explanation
Hello. Thanks for this video.
But I am working with a huge database in which more than 30 tables are referencing the primary key of a table. In this case is there any alternate way of deleting entries from child table without writing explicit 30 delete commands for 30 child tables?
Removing FK constraints or enabling cascading deletes isn't really using a query to delete from multiple tables.
The last example is more work than just deleting the data via multiple queries. I was hoping for a cool trick where you could use a delete force command or something. I don't want to spend more time to delete.
Awesome. Your video always gives good concept. Whenever I face confusion then I use to watch your videos.
Hi Kudvenkat, someone doing Frodd with your name. Please reply
good
It is specific to Microsoft SQL or applicable for oracle also??
Oh wow, the organization must be seriously downsizing if they are deleting entire departments 😊 Good explanation though.
Hello Sir, every topic you cover is something Motivational to me. I have a question, suppose we have multiple duplicate entries in the table A with identity column and also have foreign key reference constant with table B having multiple duplicates inserted into table B. Now how to delete duplicate rows from both the tables without changing to cascade
Hello Naveen - I think I understood your question. However, just to make sure I have got it correct, can you please email me the sample tables with data in excel to kudvenkat@gmail.com. I will try to record and upload a video as quickly as I can.
@@Csharp-video-tutorialsBlogspot thank you sir, nice of you responded very quick, I will send all the details in an email.
Hi venkat could you pease make video on fill factor and table partition
Only one key from composite primary key as foreign key- is this possible in sql server?
what if we want to delete master data but don't want to lose transaction data?is it possible?if not, what is your suggestion?
Which one you suggest? soft delete (isDeleted) or hard delete?
Is there any method to track or retrieve deleted rows?
Hi Akash - Good question. I always prefer soft delete over hard delete, just in case you need to undo the delete in future. Most data these days is soft deleted except where regulations doesn't allow that. If it's a hard delete, I don't think there is an easy way to recover deleted data.
You could use an After Delete DML trigger to store the deleted records in an audit table, or just use a temporal table.
Thank you Simon, yes, temporal table is a great option. Thank you for your time.
Thanks both of you. 💕 I don't know about temporal table and DML trigger, but surely I'll do a research on them.
Wat if both primary and foriegn key on the same table i.e.. a primary code that will be a parent for another primary code...plz reply sir
Same approach.
Did I get it correctly, that cascading policy should be given when we create a table and is not changeable afterwards?
Everything about a table can be changed afterward with an ALTER TABLE (as long as you don't create a conflict by this action). In fact this video showed how you could add the "ON DELETE CASCADE" by dropping the initial foreign key constraint then adding it back with a ON DELETE CASCADE specification.
Of course for those kind of policy, it would probably be best if you thought about it beforehand and imposed it from the get go (you should modify your schema anyway).
@@chaddaifouche536 Thank you so much for your response. I'll try it out right now.
In your video @7:36 you are deleting the child records first and then the parent record, so why do you need to remove the FK first?
Please sir upload video on if absentees of a particular student greater than certain condition then automatically alert message sent to that student that you are going to be dissmiss ( as.net mvc , sql server)
Hey, can you record video about all kind of table joins (inner, outer, left)etc.
Hello Szymon - The joins are discussed in the following video. Hope this helps.
czcams.com/video/wW4xcQ3FFp4/video.html
Hi Vankat,
Can you please make video on Temporal table
Hey Mohammad - Temporal Table is a great concept. Thank you for asking. Sure, I will record and upload very soon. Great question Mohammad.
@@Csharp-video-tutorialsBlogspot thank you for the response
Can you please teach us asp boilerplate
When a record from parent table is to be deleted... Can we update a default value to be updated for the columns in the child tables instead of deleting them?? Can you explain that if that's possible?
Hi Sriharsha - Yes, the related records in the child table can be updated with default or null values. We discussed this in detail in our SQL Server tutorial. The following is the link to the video. Hope it helps.
czcams.com/video/ETepOVi7Xk8/video.html
@@Csharp-video-tutorialsBlogspot Thank you sir, that really helped...
How to write a query to display the most repeated value in a column?
Wow....Great question Jalal. I will record and upload a video as soon as I can. Thank you for asking. In the meantime, the following is the query.
CREATE TABLE Students
(
NAME NVARCHAR(50)
)
GO
INSERT INTO Students VALUES ('Sara')
INSERT INTO Students VALUES ('Tom')
INSERT INTO Students VALUES ('Mike')
INSERT INTO Students VALUES ('Tom')
GO
SELECT TOP 1 NAME, COUNT(Name) AS Total
FROM Students
GROUP BY Name
ORDER BY Total DESC
-- If you just want the value use the SQL query below
SELECT TOP 1 NAME
FROM Students
GROUP BY Name
ORDER BY COUNT(Name) DESC
@@Csharp-video-tutorialsBlogspot thank you soo much sir
I have one doubt. Anyone can explain? He added constraint again. But where he took that constraint? Because drop constraint I could understand. But add constaint where he took?
Hi Venkat can you make a videos on Snowflake Please with real time scenario's ? Please sir
While deleting tha large data log size is increasing even its in simple recovery any solution for how to limit the log size increasing??
Hi sir, faced one question in interview like how to show only top 5 salary record in the result.
They don't want top 5 command and something else they expected.
Please help
@sandeep kumar Please write "select salary from table order by salary desc limit 5;" I guess this should work
@@gagandeepgogna thank you so much, will try this query
Hi Venkat. How to find out number of weekends in given month. How we can do that? Thanks in advance
Great question Ankith. Was this asked in an interview? I will record a video and upload soon. Thank you for asking.
@@Csharp-video-tutorialsBlogspot Thank you very much for response venkat. Yes in interview this was asked.
You can loop through no of days in a month , then use IF DatePart(WEEKDAY,@Start_Date) = 1 or DatePart(WEEKDAY,@Start_Date) = 7 SET then increment counter @count=@count+1
Please I need a T-SQL script to delete multiple or all user databases in a server/instance
dear sir, hur man send email to customer email when his subscription come near to ending, thanks
Why you didn't deleted the HR depatrment?
Hahaha......no specific reason to be honest. Hope I did not offend you by deleting the IT department 😀
Hi Kudvenkat. Thanks for the video. Can you please make a video to show how to insert into, select delete and update several one to many tables. For example a table student, teacher, address, phone and email. Both the teacher and the students can have several phone number email, phone and address. Thank you
How to use sql view in asp.net mvc?
Anyone knows if exist some way to add intellisense code completion and auto-format T-SQL code in SQL Server Management Studio?
If your willing to spend, try checking Red gate SQL Prompt. The best intellisense and TSQL formatter I've used so far.
Assume I have 15 columns I need to exclude only two columns in select
So writing 13 columns takes time and increase size of query
How to make short and concise
Hello Syedmujeeb - Good question. I don't think there is an easy way to specify an exclude list other than creating a view with the columns you want and selecting from it or using temp tables which is more tedious than just including the list of columns you want.
Misleading title - altering the foreign key (which you don't always have permission to do) is NOT an "sql query to delete from multiple tables" like you have in oracle...
Hi Sir, I'm a 31 Y.O. undergraduate and unable to get a job because of my graduation status. I'm an OCA with good SQL knowledge. I'm stuck with 20K salary for the past few years. I've learnt alot from you. Please help me.
Why don’t you deliver dinners to IT companies and leave your business card in those companies, maybe someone will like your attitude and you will get better job.
Cascade Delete
Child First then -> Parent
Not sure what the goal is? "I should be able to cascade delete"? If yes, then it means I don't care of the implications or best practices, but I simply love to delete anything that comes in the way :)
anyone thinks the intro music has a Sopranos theme feel? Either way awesome
Gender its just M and F.
Delete cascade is a terrible thing to be adding, especially on large databases. You give users the ability to wipe out data they might not want to.
Hey there sir. Big fan of the works, would really appreciate if you could also teach us advanced sql server. Thank You
Hey Akash - Thank you very much for taking the time to provide feedback. Many of the advanced concepts are already covered in our SQL Server tutorial. The following is the link. Hope you will find it useful.
czcams.com/play/PL08903FB7ACA1C2FB.html
Please let me know if you have a specific concept in your mind that you want me to explain. All the very best with everything you are doing.
@@Csharp-video-tutorialsBlogspot Thank You sir