Skip to main content

Posts

Showing posts with the label update

SQL Joins

1. Login in to the mysql client using the login name and password provided by your instructor (if not already logged in). 2. View the existing databases that the MySQL Server is maintaining. SHOW DATABASES; 3. Utilize the photo_album database. USE photo_album; ---- If Need be, recreate the database and load the SQL data in that will be used for this lab CREATE DATABASE photo_album; SOURCE /tmp/photo_album.sql; 4. Query the database to display the active primary customer (email, first and last name) and all the associated active login names associated with that customer. SELECT customers.email_customer, customers.fname, customers.lname, logins.login_name  FROM customers, logins WHERE customers.email_customer = logins.email_customer  AND customers.active_customer = 'True'  AND logins.active_login = 'True';

Multi-Table UPDATE and DELETE Statements

MySQL allows the use of join syntax in UPDATE and DELETE statements to enable updates or deletes that involve multiple tables. They can be useful for storage engines where transactions and foreign keys are not supported, and to make sure that the tables are consistent. These statements can be used to perform the following operations: • Update rows in one table by transferring information from another table • Update rows in one table, determining which rows to update by referring to another table • Update rows in multiple tables with a single statement • Delete rows from one table, determining which rows to delete by referring to another table • Delete rows from multiple tables with a single statement Some of the principles involved in writing joins in SELECT statements also apply to multiple-table UPDATE and DELETE statements. This section provides a brief overview of their syntax. A multiple-table UPDATE is an extension of a single-table statement: • Following the UPDAT

UPDATE with SET

To renumber the id values to being at 1, the following UPDATE statement: UPDATE people SET id=id-1; Row contents will be as follows; +-------------------+ | id | name | age | +-------------------+ | 2 | Susan | 15 | | 1 | Victor | 21 | +-------------------+