Update Join in SQL
You can use the UPDATE command in SQL to update the data entered in a single database table. Nonetheless, the UPDATE… JOIN clause must be used in order to update the data across several database tables.
Example:
Multiple tables, including student records, laboratory records, canteen passes, etc., need to have their information updated if a student desires to adjust their primary phone number in their organizational database. You may merge all of these tables into one using the JOIN clause, and you can simultaneously change the student data in each table with the change command.
Syntax
UPDATE table(s)
JOIN table2 ON table1.join_column = table2.join_column
SET table1.column1 = table2.new_value1,
table1.column2 = table2.new_value2;
Example
Create Table:
CREATE TABLE CUSTOMERS (
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);
Now, insert values into this table using the INSERT statement:
INSERT INTO CUSTOMERS VALUES
(1, ‘Mesh’, 32, ‘Bad’, 3000.00 ),
(2, ‘Khilan’, 25, ‘Delhi’, 1600.00 ),
(3, ‘Kaushik’, 23, ‘Kota’, 2700.00 ),
(4, ‘Chaitali’, 25, ‘Mumbai’, 9500.00 ),
(5, ‘Hardik’, 27, ‘Bhopal’, 200.00 ),
(6, ‘Mal’, 22, ‘Hybad’, 4500.00 ),
(7, ‘Muffy’, 24, ‘Indore’, 22000.00 );
Create Table:
CREATE TABLE ORDERS (
OID INT NOT NULL,
DATE VARCHAR (20) NOT NULL,
CUSTOMER_ID INT NOT NULL,
AMOUNT DECIMAL (18, 2)
);
INSERT statement, insert values into this table:
INSERT INTO ORDERS VALUES
(102, ‘2009-10-08 00:00:00’, 3, 3000.00),
(100, ‘2009-10-08 00:00:00’, 3, 1500.00),
(101, ‘2009-11-20 00:00:00’, 2, 1560.00),
(103, ‘2008-05-20 00:00:00’, 4, 2060.00);
UPDATE… JOIN query increments the salary of customers by 300 with
respect to the inflation of their order amount by 800
UPDATE CUSTOMERS
JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
SET CUSTOMERS.SALARY = CUSTOMERS.SALARY + 1000,
ORDERS.AMOUNT = ORDERS.AMOUNT + 500;
Verification:
SELECT * FROM CUSTOMERS;
SELECT * FROM ORDERS;
Update Join with Where Clause
Syntax
UPDATE table(s)
JOIN table2 ON column3 = column4
SET table1.column1 = value1, table1.column2 = value2, …
WHERE condition;
Example
UPDATE CUSTOMERS
LEFT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
SET CUSTOMERS.SALARY = CUSTOMERS.SALARY + 1000
WHERE ORDERS.CUSTOMER_ID = 3;
Verification
SELECT * FROM CUSTOMERS;
Update Join in SQL Server
Syntax:
UPDATE tables(s)
SET column1 = value1, column2 = value2, …
FROM table1
JOIN table2 ON table1.join_column = table2.join_column;
Example:
UPDATE CUSTOMERS
SET SALARY = SALARY + 1000
FROM CUSTOMERS
JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;
Verification:
SELECT * FROM CUSTOMERS;
