Data Manipulation Statements

Part:   1  2  

(Continued from previous part...)

If you run the code, you will get:

---
User table:
Login   Password
herong  8IS3KOXW
mike    NULL
herong  8IS3KOXW
mike    NULL
NULL    __herong
NULL    __mike
NULL    __herong
NULL    __mike

The output looks alright to me.

Update Statements

An update statement allows you to update columns of existing rows of an existing table. The syntax of an update statement is:

UPDATE tbl_name SET column=expression, column=expression, ... 
   [WHERE clause]

If executed, all rows that satisfy the condition in the where clause will be updated. Each specified column will be assigned with the value resulting from the specified expression.

Notes:

• Expressions are evaluated and resulting values are assigned to columns sequentially from left to right.
• Newly assigned values in previous columns can be used in later expressions.
• If no "where clause" is specified, all rows will be updated.

Here is an example SQL code, UpdateRows.sql, showing you how to update rows in an existing table:

-- UpdateRows.sql
-- Copyright (c) 1999 by Dr. Herong Yang
--
DROP TABLE IF EXISTS User;
CREATE TABLE User (Login VARCHAR(8), Password CHAR(8));
INSERT INTO User VALUES ('herong','8IS3KOX');
INSERT INTO User (Login) VALUES ('mike');
UPDATE User SET Password = 'IMCIAUS' WHERE Login = 'mike';
UPDATE User SET Login = CONCAT(Login,'_'),
   Password = CONCAT(Password,CHAR_LENGTH(Login));
SELECT 'User table:' AS '---';
SELECT * FROM User;

If you run the code, you will get:

---
User table:
Login   Password
herong_ 8IS3KOX7
mike_   IMCIAUS5

The output looks alright to me.

Delete Statements

A delete statement allows you to delete existing rows from an existing table. The syntax of a delete statement is:

DELETE FROM tbl_name [WHERE clause]

If executed, all rows that satisfy the condition in the where clause will be deleted. If no "where clause" specified, all rows will be deleted.

Here is an example SQL code, DeleteRows.sql, showing you how to delete rows from an existing table:

-- DeleteRows.sql
-- Copyright (c) 1999 by Dr. Herong Yang
--
DROP TABLE IF EXISTS User;
CREATE TABLE User (Login VARCHAR(8), Password CHAR(8));
INSERT INTO User VALUES ('herong','8IS3KOX');
INSERT INTO User (Login) VALUES ('mike');
DELETE FROM User WHERE Login = 'herong';
SELECT 'User table:' AS '---';
SELECT * FROM User;

If you run the code, you will get:

---
User table:
Login   Password
mike    NULL

The output looks alright to me.

Part:   1  2