Remove data from tables

Question

Post reply

Remove data from tables

chgn01

SSCarpal Tunnel

Points: 4354
More actions
March 4, 2015 at 8:24 pm

#298392

Comments posted to this topic are about the item Remove data from tables
--------------------------------------
;-)“Everything has beauty, but not everyone sees it.” ― Confucius

Viewing 15 posts - 1 through 15 (of 51 total)

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

mccork SSC-Insane Points: 22288 More actions · Answer 1

I suspect answer D (one of the correct ones) needs to be "DELETE FROM" rather than "DELETE TABLE".

Cheers,
- Mark

Jostein Saethern SSCommitted Points: 1861 More actions · Answer 2

Jostein Saethern

SSCommitted

Points: 1861

March 4, 2015 at 8:56 pm

#1781820

The "delete table BBB;" statement in d) will fail.

karthik babu Hall of Fame Points: 3335 More actions · Answer 3

Got Lucky as I chose (d) where no other possible option is left!! 😀

Thanks for the question.. Often I use the similar queries in our pre-production environment to generate new records... 🙂

______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂

divu.17 SSC Enthusiast Points: 130 More actions · Answer 4

"Delete table" is not a correct syntax in option d.

Correct and is option c and f.

Thanks,

Divya.

Mauricio_ SSCrazy Points: 2935 More actions · Answer 5

I only marked option F). Option D) has a wrong syntax. It should be delete from BBB; delete from AAA;

robert.seabridge 92235 Mr or Mrs. 500 Points: 597 More actions · Answer 6

Same as, D would not work so my answer of F is then correct 😀

Hany Helmy SSChampion Points: 13921 More actions · Answer 7

Jostein Saethern (3/4/2015)
The "delete table BBB;" statement in d) will fail.

Its just a typo.

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

Hany Helmy SSChampion Points: 13921 More actions · Answer 8

divu.17 (3/5/2015)
"Delete table" is not a correct syntax in option d.
Correct and is option c and f.
Thanks,
Divya.

C is not correct, cause you can`t delete any rows from table AAA until first you delete all referenced rows in table BBB.

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 9

Nice question, unfortunately poorly implemented.

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 10

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.

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

Toreador SSChampion Points: 11365 More actions · Answer 11

Even ignoring the typo/mistake, the explanation is totally wrong. It is nothing to do with whether the statements are logged, and it is common incorrect) myth that TRUNCATE TABLE is not logged. I'm slightly surprised that this misleading assertion slipped through the net.

The reason that none of the TRUNCATE AAA statements will work is that you cannot truncate a table that is referenced by a foreign key. This is true irrespective of whether there is any data in it - you can verify this by simply creating the tables, not populating them, and trying to TRUNCATE TABLE AAA.

Mauricio_ SSCrazy Points: 2935 More actions · Answer 12

Toreador (3/5/2015)
Even ignoring the typo/mistake, the explanation is totally wrong. It is nothing to do with whether the statements are logged, and it is common incorrect) myth that TRUNCATE TABLE is not logged. I'm slightly surprised that this misleading assertion slipped through the net.
The reason that none of the TRUNCATE AAA statements will work is that you cannot truncate a table that is referenced by a foreign key. This is true irrespective of whether there is any data in it - you can verify this by simply creating the tables, not populating them, and trying to TRUNCATE TABLE AAA.

+1

Richard Warr SSCertifiable Points: 6966 More actions · Answer 13

No such command as "DELETE TABLE". I thought that was the catch so went for the two truncates (against my better judgement). There was only one correct answer.

patricklambin SSCrazy Eights Points: 9994 More actions · Answer 14

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