This confused me for a few minutes today and I wanted to share to help avoid further confusion.
The specific code was this:
DELETE t2 FROM [dbo].[Table1] t1 INNER JOIN [dbo].[Table2] t2 on t1.favColor = t2.id;
Names have been changed to protect the innocent.
In the above delete statement which table will have rows deleted from it?
A: Table1
B: Table2
C: Both Table1 and Table2
D: Neither Table1 and Table2
During my 24 Hours of Pass presentation on Advanced CTE’s today I was asked the question about deleting from a CTE when it uses an EXISTS… Read More »Deleting from a CTE with an EXISTS statement
On Wednesday June 24th at 6:00pm pacific time or( 25 Jun 2015 01:00 GMT ) will be speaking at the 24HOP (24 Hours of Pass) conference which… Read More »Advanced Common Table Expressions – 24 Hours of PASS
The seventh week of ten in the Database Corruption Challenge went well. There were a few new participants, and many of those who have competed week over week. The first to submit a correct answer this week was Raul Gonzalez.
What was interesting about this challenge was that it was not your typical corruption where you could just run DBCC CheckDB and find the problem. To understand exactly what happened, solving this required reading through the transaction log that had not yet been backed up and finding the specific problem.
The original post describing the challenge stated the following:
Your goal if you choose to accept it is to get restore all the data in the [OPEN_NFIRS].[Record1000] table prior to its disappearance. Note: there may have been multiple DBA’s working on this system in several different time zones across the world.
Most DBAs know the usual difference between TRUNCATE and DELETE FROM, however one not so obvious difference between the two is how things are handled if the table is corrupt.
For instance, if you look at the corrupt database from the Database Corruption Challenge Week 1, you see the following
If you want to clear out the corrupt Revenue table, one way to attempt to do it is with a DELETE statement like this.
Read More »Difference Between TRUNCATE TABLE and DELETE FROM Table
I will be attending SQL Saturday #265 in Portland Oregon on November 16th 2013. This will be my second time attending SQL Saturday in Portland,… Read More »SQL Saturday #265 in Portland Oregon
I used to be able to do the Common Table Expression in a single 1 hour session, but after writing the book on Common Table… Read More »Common Table Expressions Presentations Updated.
My Common Table Expression presentation was accepted for SQL Saturday 212 in Redmond WA. This is the session that I am the most familiar with,… Read More »Common Table Expression accepted for SQL Saturday 212 in Redmond WA