SQL DELETE

5 Key excerpts on "SQL DELETE"

• 

  eBook - ePub

  Joe Celko's SQL for Smarties

  Advanced SQL Programming

  • Joe Celko(Author)
  • 2010(Publication Date)
  • Morgan Kaufmann

    (Publisher)

  15 Table Operations

  There are only four things you can do with a set of rows in an SQL table: insert them into a table, delete them from a table, update the values in their columns, or query them. The unit of work is a set of whole rows inside a base table.

  When you worked with file systems, access was one record at a time, then one field within a record. Since you had repeated groups and other forms of variant records, you could change the structure of each record in the file.

  The mental mode in SQL is that you grab a subset, as a unit, all at once in a base table and insert, update, or delete, as a unit, all at once. Imagine that you have enough computer power that you can allocate one processor to every row in a table. When you blow your whistle, all the processors do their work in parallel.

  15.1 DELETE FROM Statement

  The DELETE FROM statement in SQL removes zero or more rows of one table. Interactive SQL tools will tell the user how many rows were affected by an update operation and Standard SQL requires the database engine to raise a completion condition of “no data” if there were zero rows. There are two forms of DELETE FROM in SQL: positioned and searched. The positioned deletion is done with cursors; the searched deletion uses a WHERE clause like the search condition in a SELECT statement.

  15.1.1 The DELETE FROM Clause

  The syntax for a searched deletion statement is:

  <delete statement: searched> ::= DELETE FROM <target table> [[AS] <correlation name>] [WHERE <search condition>]

  The DELETE FROM clause simply gives the name of the updatable table or view to be changed. Notice that a correlation name is allowed in the DELETE FROM

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Beginning ASP.NET 4.5: in C# and VB

  • Imar Spaanjaars(Author)
  • 2012(Publication Date)
  • Wrox

    (Publisher)

  Although this chapter had a strong focus on the SQL that you need to write to access a database, you see in the next chapter that in many cases Visual Studio makes accessing databases pretty easy as well by generating most of the code for you. However, a solid knowledge of SQL helps you in understanding and tweaking the code that is being written for you.

  Exercises

  1. If you try to delete a row from the Genre table that has matching rows in the Review table, the DELETE statement fails. How is this possible?

  2. If you try to delete a row from the Review table that has its GenreId set to the Id of an existing genre in the Genre table, the DELETE statement succeeds. Why?

  3. Imagine you want to clean up your database and decide to delete all rows from the Review table that have an Id of 100 or less. Write a SQL statement that deletes these rows.

  4. Imagine you want to delete the genre with an ID of 4. But before you delete the genre, you want to reassign reviews assigned to this genre to another genre with an ID of 11. What SQL statements do you need to accomplish this?

  5. Write a SQL statement that updates the Rock genre to read Punk Rock instead. You have at least two ways to write the WHERE clause for this statement.

  You can find answers to these exercises in Appendix A.

  What You Learned in this Chapter

  CRUD	The four basic SQL operations to work with data in a database: Create, Read, Update, and Delete
  Foreign key	Identifies a column in a table that refers to the primary key of another table to enforce referential integrity
  Identity	An automatic, sequential number assigned to new rows
  JOIN	Enables you to express the relationship between two or more tables in a query to find related data
  Primary key	Consists of one or more columns in a table that uniquely identify a row in that table
  Relational database	A type of database where data is stored in separate, spreadsheet-like tables that can refer to each other
  Relationship	Defines the relation between one or more tables and helps you enforce referential integrity
  Table	An object in a database that enables you to store data

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Database Management Systems

  • Sotirios Zygiaris(Author)
  • 2018(Publication Date)
  • Emerald Publishing Limited

    (Publisher)

  Inserting data is a critical process regarding the accountability of the database. Data insertion must follow quality-designed insertion procedures to avoid erroneous and incomplete resulting information. When inserting into a table, the number of rows is increased by one. The SQL INSERT command inserts a single row of data into a table. When inserting data into a table, all the constraints that are specified during data creation must be applied.

  When the data of the database are deleted or updated (modified), the database is changing. Careful steps must be taken when these activities are invoked to minimize the risk of losing valuable data. When deleting from a table, the number of rows is reduced by one or many. To delete data from a database, the DELETE SQL command is used, which may delete one or many rows or even the entire set of rows from a table.

  Updating or modifying the data of a database means that certain fields of a row or rows will change. When updating a table, the number of rows remains the same. Only the data values inside the fields of the rows may change. The UPDATE SQL command is used to modify data. The integrity constraints CASCADE/RESTRICT/SET NULL/SET DEFAULT are applied to foreign keys and are included in the CREATE TABLE SQL command.

  The role of these constraints is to ensure referential integrity; that means that the relationships between tables remain consistent; data and foreign keys must be consistent with the data in primary keys. Since by definition a foreign key is referenced to a primary key in another table, this key must exist in the primary table.

  The ALTER TABLE SQL command is to modify the structure column definitions of a table. In general, CREATE TABLE and ALTER TABLE privileges in a database management system must be carefully granted. Usually, only database administrators have the right to make some modifications to the database.

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Beginning Visual Basic 2012

  • Bryan Newsome(Author)
  • 2012(Publication Date)
  • Wrox

    (Publisher)

  DELETE statement looks like this:

    DELETE FROM Teacher Or this:   DELETE Teacher

  This statement deletes every row in the table. You may want to delete just one row or maybe only certain rows. In this case, you just add the WHERE clause—the same statement that you learned using the SELECT statement. The FROM keyword is optional; use it if you think the SQL is more readable.

    DELETE FROM Teacher WHERE TeacherID = 4

  The previous code deletes the row that has the TeacherID of 4 from the table.

  You can summarize the basic DELETE syntax in the following way:

    DELETE [FROM] table-name [WHERE search-condition]

  Using the INSERT Statement

  To insert data in the database, you use the SQL INSERT statement. The basic INSERT statement looks like this:

    INSERT INTO Teacher(Name, Address, Zip) VALUES ('Bryan', '123 Main Street', '20211') Or this:   INSERT Teahcer(Name, Address, Zip) VALUES ('Bryan', '123 Main Street', '20211')

  This statement inserts one row in the table. As you can see, the INTO keyword is optional, and you should use it when it makes your code more readable. Sometimes, you need to insert more than one row at a time. You do this a lot when loading data into a temp. To load data like this, you can SELECT data from another table and INSERT it into a table:

    INSERT INTO Teacher_Temp(Name, Address, Zip) (SELECT Name, Address, Zip from Teacher WHERE Zip = '20211')

  The previous code inserts all rows that have a zip of 20211 from Teacher into Teacher_temp .

  You may find that you need to enter data that already exists in a table, but one column must be changed to a new value.   INSERT INTO Teacher(ID, Name, Address, Zip, InsertUser) (SELECT 'AX0001', Name, Address, Zip, 'Bryan' from Teacher_Temp WHERE ID = 'QX1278')

  The previous SQL code inserts a row into Teacher

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Beginning Visual Basic 2015

  • Bryan Newsome(Author)
  • 2015(Publication Date)
  • Wrox

    (Publisher)

  DELETE statement looks like this:

  DELETE FROM Teacher Or this: DELETE Teacher

  This statement deletes every row in the table. You may want to delete just one row or maybe only certain rows. In this case, you just add the WHERE clause—the same statement that you learned using the SELECT statement. The FROM keyword is optional; use it if you think the SQL is more readable.

  DELETE FROM Teacher WHERE TeacherID = 4

  The previous code deletes the row that has the TeacherID of 4 from the table.

  You can summarize the basic DELETE syntax in the following way:

  DELETE [FROM] table-name [WHERE search-condition]

  Using the INSERT Statement

  To insert data in the database, you use the SQL INSERT statement. The basic INSERT statement looks like this:

  INSERT INTO Teacher(Name, Address, Zip) VALUES ('Bryan', '123 Main Street', '20211') Or this: INSERT Teacher(Name, Address, Zip) VALUES ('Bryan', '123 Main Street', '20211')

  This statement inserts one row in the table. As you can see, the INTO keyword is optional, and you should use it when it makes your code more readable. Sometimes, you need to insert more than one row at a time. You do this a lot when loading data into a temp. To load data like this, you can SELECT data from another table and INSERT it into a table:

  INSERT INTO Teacher_Temp(Name, Address, Zip) (SELECT Name, Address, Zip from Teacher WHERE Zip = '20211')

  The previous code inserts all rows that have a Zip of 20211 from Teacher into Teacher_temp .

  You may find that you need to enter data that already exists in a table, but one column must be changed to a new value. INSERT INTO Teacher(ID, Name, Address, Zip, InsertUser) (SELECT 'AX0001', Name, Address, Zip, 'Bryan' from Teacher_Temp WHERE ID = 'QX1278')

  The previous SQL code inserts a row into Teacher and copies data from the row with ID QX1278 . The difference is that you assign a new ID of AX0001 and the InsertUser of Bryan

  Sign up to read

  Learn more about book