Remove data from tables

Question

Remove data from tables

Viewing 15 posts - 16 through 30 (of 51 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

patricklambin (3/5/2015)
Why the b) choice ( truncate BBB , delete AAA ) is not correct ?
It seems for me that as soon as there is no more rows in the BBB table , it is possible to execute a truncate or delete on the table AAA.
Maybe , I have missed something about the behaviour of truncate/delete in case of foreign key.
According to me , the b , d , f choices are correct. Always do a delete/drop on the BBB table with the foreign key before do a delete/drop on the AAA table.
Or , I have misunderstood the meaning of the b choice ( I recognized that my English understanding is far to be good ).
Have a nice day

The b) choice is TRUNCATE TABLE BBB, TRUNCATE TABLE AAA.

Since there is a foreign key constraint between BBB and AAA (with AAA being the parent table), you can never truncate AAA without dropping the constraints first.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Toreador SSChampion Points: 11358 More actions · Answer 2

patricklambin (3/5/2015)
Why the b) choice ( truncate BBB , delete AAA ) is not correct ?

The b) choice is not delete AAA, it is truncate[/I] AAA. This will fail for the reason I gave in my previous reply.

ondo SSC Enthusiast Points: 153 More actions · Answer 3

Just a typo makes here difference if you answer correctly or not. I didn't pick d), because syntax was not correct ...

SqlMel SSCrazy Points: 2901 More actions · Answer 4

Koen Verbeeck (3/5/2015)
Nice question, unfortunately poorly implemented.

+1

---------------
Mel. 😎

SqlMel SSCrazy Points: 2901 More actions · Answer 5

Koen Verbeeck (3/5/2015)
Hany Helmy (3/5/2015)
Jostein Saethern (3/4/2015)
The "delete table BBB;" statement in d) will fail.
Its just a typo.
That's not a typo, that is a mistake.
DELETE FRAM BBB, that is a typo.

+2

---------------
Mel. 😎

m mcdonald Hall of Fame Points: 3264 More actions · Answer 6

Hany Helmy (3/5/2015)
Jostein Saethern (3/4/2015)
The "delete table BBB;" statement in d) will fail.
Its just a typo.

I hate having to guess what was a typo and what is intentionally incorrect

SqlMel SSCrazy Points: 2901 More actions · Answer 7

... but TRUNCATE statement do not logging.

Oh boy, here we go again...

---------------
Mel. 😎

djj SSCoach Points: 18831 More actions · Answer 8

Good question. Answered before testing and got it wrong. (truncate/truncate) 🙁

Need to remember this.

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 9

As everyone else has already pointed out, d) is wrong. "delete table BBB;' should be 'delete from BBB;' instead. Everyone that answered f) as the only correct answer should get a point. This was an easy one too.

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 10

Typo or not, it is still an error and will not work. So the answer is only f). I understand it can be difficult to come up with these questions and I applaud those who do. I couldn't do it. But I would encourage those who do create questions like this, where the code can be easily tested, to do just that, test it, before publishing. Yes it was a cut and paste error, a line didn't get changed that should have been. We do it all the time. What we try not to do is go to production without testing that code at least once. Had the author tried executing their stated correct answer d) then they would have seen the error prior to publishing the question of the day.

djj SSCoach Points: 18831 More actions · Answer 11

Iwas Bornready (3/5/2015)
Typo or not, it is still an error and will not work. So the answer is only f). I understand it can be difficult to come up with these questions and I applaud those who do. I couldn't do it. But I would encourage those who do create questions like this, where the code can be easily tested, to do just that, test it, before publishing. Yes it was a cut and paste error, a line didn't get changed that should have been. We do it all the time. What we try not to do is go to production without testing that code at least once. Had the author tried executing their stated correct answer d) then they would have seen the error prior to publishing the question of the day.

delete FROM BBB;

delete from AAA;

Worked for me. How did you test it?

Toreador SSChampion Points: 11358 More actions · Answer 12

SqlMel (3/5/2015)
... but TRUNCATE statement do not logging.
Oh boy, here we go again...

Unfortunately the discussion seems to be concentrating on the typo/syntax error instead 🙁

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 13

djj (3/5/2015)
Iwas Bornready (3/5/2015)
Typo or not, it is still an error and will not work. So the answer is only f). I understand it can be difficult to come up with these questions and I applaud those who do. I couldn't do it. But I would encourage those who do create questions like this, where the code can be easily tested, to do just that, test it, before publishing. Yes it was a cut and paste error, a line didn't get changed that should have been. We do it all the time. What we try not to do is go to production without testing that code at least once. Had the author tried executing their stated correct answer d) then they would have seen the error prior to publishing the question of the day.
delete FROM BBB;
delete from AAA;
Worked for me. How did you test it?

That's not what the question had.

It has:

delete TABLE BBB;

delete from AAA;

which is invalid SQL.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Richard Warr SSCertifiable Points: 6963 More actions · Answer 14

We had no way of telling if DELETE TABLE meant DELETE FROM or DROP TABLE. Both would have worked though 🙂

PurpleLady SSCertifiable Points: 7815 More actions · Answer 15

Hany Helmy (3/5/2015)
Jostein Saethern (3/4/2015)
The "delete table BBB;" statement in d) will fail.
Its just a typo.

But the type changed the result - DELETE TABLE will not work.