SQL - UPDATE JOIN

Syntax

The following is the basic syntax of the UPDATE... JOIN声明——

UPDATE table(s)
SET column1 = value1, column2 = value2, ...
FROM table1 
JOIN table2 ON column3 = column4;

当我们在这里说JOIN时,我们可以使用任何类型的Join:经常Join、自然Join、Inner Join、Exer Join、左派Join、Join、Ret Join、Ret Join、Alfonin、Rein、Ad Join等。

Example

假设我们已经建立了一个名为“客户”的表格,其中载有客户的个人详情,包括姓名、年龄、地址和工资等,使用以下询问:

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 as follows −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1,  Ramesh , 32,  Ahmedabad , 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2,  Khilan , 25,  Delhi , 1500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3,  kaushik , 23,  Kota , 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4,  Chaitap , 25,  Mumbai , 6500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5,  Hardik , 27,  Bhopal , 8500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6,  Komal , 22,  MP , 4500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (7,  Muffy , 24,  Indore , 10000.00 );

该表将编成:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | Kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitap |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

让我们制定另一份表格命令,载列所发布命令的细节和日期。

CREATE TABLE ORDERS (
   OID INT NOT NULL,
   DATE VARCHAR (20) NOT NULL,
   CUSTOMER_ID INT NOT NULL,
   AMOUNT DECIMAL (18, 2),
);

采用INSERT声明,在本表中插入以下数值:

INSERT INTO ORDERS (OID, DATE, CUSTOMER_ID, AMOUNT)
VALUES (102,  2009-10-08 00:00:00 , 3, 3000.00);

INSERT INTO ORDERS (OID, DATE, CUSTOMER_ID, AMOUNT)
VALUES (100,  2009-10-08 00:00:00 , 3, 1500.00);

INSERT INTO ORDERS (OID, DATE, CUSTOMER_ID, AMOUNT)
VALUES (101,  2009-11-20 00:00:00 , 2, 1560.00);

INSERT INTO ORDERS (OID, DATE, CUSTOMER_ID, AMOUNT)
VALUES (103,  2008-05-20 00:00:00 , 4, 2060.00);

表格如下:

+-----+---------------------+-------------+---------+
| OID | DATE                | CUSTOMER_ID | AMOUNT  |
+-----+---------------------+-------------+---------+
| 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

利用UPDATE... JOIN query交叉修改多个表格——

UPDATE CUSTOMERS
SET SALARY = SALARY + 1000
FROM CUSTOMERS 
JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

Output

The output is displayed as follows −

(3 rows affected)

Verification

我们可以通过利用“选举考试”声明检索其内容,核实这些改动是否反映在表格中。

以下是在客户表中显示记录的问题。

SELECT * FROM Customers;

最新表格显示:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  2500.00 |
|  3 | kaushik  |  23 | Kota      |  3000.00 |
|  4 | Chaitap |  25 | Mumbai    |  7500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

你们必须记住,由于上述问题,我们只是试图更新客户表格,因此,订单表不会反映变化。

UPDATE… JOIN with WHERE Clause

The ON clauses in UPDATE... JOIN查询被用来对需要更新的记录施加限制。 除此以外,我们还可以使用惠灵教育条款,使制约因素更加严格。

Syntax

The syntax of it is as follows −

UPDATE table(s)
SET column1 = value1, column2 = value2, ...
FROM table1 
JOIN table2 ON column3 = column4
WHERE condition;

Example

观察如下。 在这里,我们正试图增加只赚取2000年收入的客户的工资。

UPDATE CUSTOMERS
SET SALARY = SALARY + 1000
FROM CUSTOMERS 
LEFT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
WHERE CUSTOMERS.SALARY = 2000.00;

Output

产出如下所示:

(1 row affected)

Verification

我们可以通过利用“选举考试”声明检索其内容,核实这些改动是否反映在表格中。

以下是在客户表中显示记录的问题。

SELECT * FROM Customers;

最新表格显示:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  3000.00 |
|  2 | Khilan   |  25 | Delhi     |  2500.00 |
|  3 | kaushik  |  23 | Kota      |  3000.00 |
|  4 | Chaitap |  25 | Mumbai    |  7500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Only Customers table is updated using the query above, hence, the Orders table remains unchanged.