MySQL - UPDATE JOIN 更新连接

要通过 JavaScript 程序执行更新连接，我们需要使用 mysql2 库的 query() 函数执行带有 JOIN 子句的 UPDATE 语句，如下所示 -

sql = "UPDATE Customers c JOIN Orders o ON c.ID = o.CUSTOMER_ID SET c.SALARY = c.SALARY + o.AMOUNT";
con.query(sql);

要通过 Java 程序执行更新连接，我们需要使用 JDBC 函数 executeUpdate() 执行带有 JOIN 子句的 UPDATE 语句，如下所示 -

String sql = "UPDATE tcount_tbl INNER JOIN tutorials_tbl ON tcount_tbl.tutorial_author = tutorials_tbl.tutorial_author
SET tcount_tbl.tutorial_count = tcount_tbl.tutorial_count + 100";
statement.executeUpdate(sql);

要通过 Python 程序执行更新连接，我们需要使用 MySQL Connector/Python 的 execute() 函数执行带有 JOIN 子句的 UPDATE 语句，如下所示 -

update_join_query = "UPDATE Customers c JOIN Orders o ON c.ID = o.CUST_ID SET c.SALARY = c.SALARY + o.AMOUNT"
cursorObj.execute(update_join_query)

var mysql = require("mysql2");
var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "password",
}); //连接到 MySQL

con.connect(function (err) {
  if (err) throw err;
  //   console.log("Connected successfully...!");
  //   console.log("--------------------------");
  sql = "USE TUTORIALS";
  con.query(sql);
 //UPDATE JOIN
  sql = "UPDATE Customers c JOIN Orders o ON c.ID = o.CUSTOMER_ID  SET c.SALARY = c.SALARY + o.AMOUNT";
 //displaying the updated data along with ID;
  sql =
    "SELECT c.ID, c.SALARY FROM Customers c INNER JOIN Orders o  ON c.ID = o.CUSTOMER_ID";
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});    

输出

生成的输出如下 -

[
  { ID: 3, SALARY: '5000.00' },
  { ID: 3, SALARY: '5000.00' },
  { ID: 2, SALARY: '3060.00' },
  { ID: 4, SALARY: '8560.00' }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class UpdateJoin {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String username = "root";
      String password = "password";
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
         Connection connection = DriverManager.getConnection(url, username, password);
         Statement statement = connection.createStatement();
         System.out.println("Connected successfully...!");
        //MySQL Update JOIN...!;
         String sql = "UPDATE tcount_tbl
" + 
         "INNER JOIN tutorials_tbl ON tcount_tbl.tutorial_author = tutorials_tbl.tutorial_author
" + 
         "SET tcount_tbl.tutorial_count = tcount_tbl.tutorial_count + 100
";
         statement.executeUpdate(sql);
         System.out.println("tutorial_count updated successfully...!");
        //fetch the records...!;
         ResultSet resultSet = statement.executeQuery("SELECT a.tutorial_author, b.tutorial_count FROM tutorials_tbl a INNER JOIN tcount_tbl b ON a.tutorial_author = b.tutorial_author");
         while (resultSet.next()){
            System.out.println(resultSet.getString(1)+ " "+ resultSet.getInt(2));
         }
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}  

输出

获得的输出如下所示 -

Connected successfully...!
tutorial_count updated successfully...!
John Paul 201
Sanjay 201

import mysql.connector
#建立连接
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
cursorObj = connection.cursor()
update_join_query = f"""UPDATE Customers c JOIN Orders o ON c.ID = o.CUST_ID SET c.SALARY = c.SALARY + o.AMOUNT"""
cursorObj.execute(update_join_query)
connection.commit()
print("updated successfully")
cursorObj.close()
connection.close()

输出

以下是上述代码的输出 -

updated successfully