آموزش به‌روزرسانی داده‌ها در SQL

CREATE DATABASE updateDB;
USE updateDB;

CREATE TABLE clients (
clientID int PRIMARY KEY,
name varchar(20),
routine varchar(30),
standardFee decimal (5,2)
);

CREATE TABLE shows (
showID int PRIMARY KEY,
showDate date,
clientID int,
attendance int,
ticketPrice decimal (4,2),
CONSTRAINT client_fk
FOREIGN KEY (clientID)
REFERENCES clients(clientID)
);

INSERT INTO clients VALUES
(1, 'Gladys', 'song and dance', 180),
(2, 'Catherine', 'standup', 99.99),
(3, 'Georgeanna', 'standup', 45),
(4, 'Wanda', 'song and dance', 200),
(5, 'Ann', 'trained squirrel', 79.99);

INSERT INTO shows VALUES
(1, '2019-12-25', 4, 124, 15),
(2, '2020-01-11', 5, 84, 29.50),
(3, '2020-01-17', 3, 170, 12.99),
(4, '2020-01-31', 5, 234, 14.99),
(5, '2020-02-08', 1, 86, 25);

UPDATE table_name
SET column_name = value
WHERE condition;

UPDATE clients
SET name = 'Katherine'
WHERE name = 'Catherine';

UPDATE clients
SET standardFee = 140
WHERE routine LIKE 's%';

UPDATE clients
SET standardFee = standardFee * 1.4;

UPDATE shows
SET attendance = attendance + 20,
ticketPrice = ticketPrice * 1.5
WHERE clientID IN (
SELECT clientID
FROM clients
WHERE name = 'Georgeanna' OR name = 'Wanda'
);

UPDATE clients JOIN shows
USING (clientID)
SET clients.routine = 'mime',
shows.ticketPrice = 30
WHERE name = 'Gladys';

UPDATE clients
SET clientID = 9
WHERE name = 'Ann';

ALTER TABLE shows
DROP FOREIGN KEY client_fk;

ALTER TABLE shows
ADD CONSTRAINT new_client_fk
FOREIGN KEY (clientID)
REFERENCES clients (clientID)
ON UPDATE CASCADE;

UPDATE clients
SET clientID = 9
WHERE name = 'Ann';