DELETE 1


Hint: Be carefull and deactivate AUTOCOMMIT.

The DELETE command removes rows from a table.

DELETE FROM <tablename>
WHERE       <search_condition>;

The syntax is very simple as we do not need to specify any columnname - rows are deleted as a whole and not partly. As usual the search condition specifies the criterion which identifies the affected rows. It can involve zero, one or more rows. If we omit the WHERE keyword and the search condition all rows are affected.

Example

-- Delete one row
DELETE FROM person
WHERE  lastname = 'Burton';

-- It's only a test. Restore the row.
ROLLBACK;

The information about Mr. Burton was deleted and restored again.

Further Information

We present some more information about the DELETE command here. There are also some comments to the interconnection with the TRUNCATE command.

Exercises

Delete the hobby 'Yoga'.

Click to see solution
-- Delete one row
DELETE FROM hobby
WHERE  hobbyname = 'Yoga';
-- or: WHERE  id = 6;

ROLLBACK; -- if we want to restore the row
COMMIT;   -- if we want to commit our work

-- Check the result
SELECT * FROM hobby;

Delete all relations between persons and hobbies. Check result. Restore all rows.

Click to see solution
-- compact syntax - great impact
DELETE FROM person_hobby;

-- Check the result
SELECT * FROM person_hobby;

-- restore everything
ROLLBACK;



  This article uses material from the Wikipedia page available here. It is released under the Creative Commons Attribution-Share-Alike License 3.0.

Structured_Query_Language/DELETE_1
 



 

Connect with defaultLogic
What We've Done
Led Digital Marketing Efforts of Top 500 e-Retailers.
Worked with Top Brands at Leading Agencies.
Successfully Managed Over $50 million in Digital Ad Spend.
Developed Strategies and Processes that Enabled Brands to Grow During an Economic Downturn.
Taught Advanced Internet Marketing Strategies at the graduate level.


Manage research, learning and skills at defaultLogic. Create an account using LinkedIn or facebook to manage and organize your Digital Marketing and Technology knowledge. defaultLogic works like a shopping cart for information -- helping you to save, discuss and share.


  Contact Us