Support Online
MirrorLooking Glass
GenixNodeGenixNode
Client Panel
Skip to main content

Updating SQL Data (UPDATE) Guide

Meta description: Learn how to modify existing data in your tables with the SQL UPDATE command, multi-table updates with JOIN, and FOREIGN KEY constraints.

🎯 What Will You Learn in This Guide?​

In this guide, you will learn how to edit records in your database using the UPDATE command in SQL.

  • Single table updates
  • Conditional updates with subqueries
  • Multiple table update with JOIN
  • Relational data updates with ON UPDATE CASCADE behavior

All steps are illustrated with examples in the MySQL environment.

🧠 Phase 1 – Technical Summary​

  • Main topic: Using SQL UPDATE
  • Problem it solves: Correcting incorrect or missing data, making bulk updates
  • Steps you will learn:
    1. Single table update
    2. Multi-column update
    3. Using Subquery and JOIN
    4. Foreign key constraints (ON UPDATE CASCADE, SET NULL)

⚙️ 1. Preparing the Environment​

ssh admin@tr1-node01.ornek.com
mysql -u admin -p
💬 Connect to the MySQL server.​
CREATE DATABASE updateDB;
USE updateDB;

💬 Create and activate database.

🧱 2. Creating Sample Tables​

CREATE TABLE clients (
  clientID INT PRIMARY KEY,
  name VARCHAR(20),
  routine VARCHAR(30),
  standardFee DECIMAL(5,2)
);

💬 Table that holds customer information.

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)
);

💬 Table keeping performance records.

💾 3. Add Sample Data​

INSERT INTO clients VALUES
(1, 'Kerem', 'standup', 120),
(2, 'Ayşe', 'pantomim', 90),
(3, 'Fatma', 'dans', 150);
INSERT INTO shows VALUES
(1, '2024-12-01', 1, 150, 20),
(2, '2024-12-05', 2, 220, 25),
(3, '2024-12-09', 3, 180, 30);

🔄 4. Single Table Update (Using UPDATE)​

Update Specific Record​
UPDATE clients
SET name = 'Kadir'
WHERE name = 'Kerem';

💬 He changes his name "Kerem" to "Kadir".

Bulk Update (with LIKE)​
UPDATE clients
SET standardFee = 140
WHERE routine LIKE 'p%';

💬 Updates the fee for all records whose performance type starts with “p”.

Arithmetic Update​
UPDATE shows
SET ticketPrice = ticketPrice * 1.2;

💬 Increases all ticket prices by 20%.

🔗 5. Multi-Table Update (with JOIN)​

UPDATE clients JOIN shows
USING (clientID)
SET clients.routine = 'mizah',
    shows.ticketPrice = 35
WHERE clients.name = 'Ayşe';

💬 It associates clients and shows tables with clientID and updates both tables at the same time.

🔒 6. FOREIGN KEY Updates (ON UPDATE CASCADE)​

CASCADE is used to automatically update the linked table when the primary key changes.

Remove Current Restriction​
ALTER TABLE shows DROP FOREIGN KEY client_fk;
Adding New Constraint (with CASCADE)​
ALTER TABLE shows
ADD CONSTRAINT fk_client_update
FOREIGN KEY (clientID)
REFERENCES clients(clientID)
ON UPDATE CASCADE;

💬 Now, if the ID in the clients table changes, the shows table is also updated.

❓ Frequently Asked Questions (FAQ)​

  1. What happens if I don't use WHERE?

All rows are updated, so be careful.

  1. Does updating with JOIN work on all databases?

Supports MySQL, different syntax may be required on some systems.

  1. What is ON UPDATE CASCADE?

It automatically reflects changes in the main table to the subtable.

  1. What does subquery UPDATE do?

It helps determine conditions dynamically and facilitates complex operations.

  1. How do I see post-UPDATE alerts?

SHOW WARNINGS; You can use the command.

☁️ Conclusion and Recommendation​

In this guide, you learned how to update data using the UPDATE command in SQL, multiple table operations with JOIN, and how to use ON UPDATE CASCADE. You can now make secure data updates to related tables. 💡 Now test these examples in a live environment by setting up a MySQL server on GenixNode.