php - 带有联接的 SQL 更新失败

Question

我有此查询并返回错误显示“您的 SQL 语法有错误；请检查与您的 MySQL 服务器版本对应的手册，了解在‘FROM Sales JOIN Sales ON Inventory.saleID 附近使用的正确语法=Sales.ID JOIN Customers ON Sales.custo' 在第 1 行

这是我的查询。我知道我没有使用别名，所以它看起来很难看，但语法似乎是正确的，我传入的变量不为空，我已经回复了它们。

 $sql = "UPDATE Sales SET salequantity='$takeCount' 
       FROM `Sales` 
       JOIN Sales ON Inventory.saleID=Sales.ID 
       JOIN Customers ON Sales.customerID=Customers.ID 
       JOIN Products ON Inventory.productID = Products.ID 
      JOIN Categories ON Products.categoryID=Categories.ID 
      WHERE Customers.ID='$customersID' AND Products.ID='$id'";

这是我的模式

CREATE TABLE Categories
(
ID int NOT NULL auto_increment,
type varchar(255),
description varchar(255),
PRIMARY KEY (ID)
);

CREATE TABLE Products
(
ID int NOT NULL auto_increment,
categoryID int,
customerID int,
name varchar(255),
description varchar(255),
price float(10),
quantity int(10),
PRIMARY KEY (ID),
FOREIGN KEY (categoryID) REFERENCES Categories(ID)
);
CREATE TABLE Customers
(
ID int NOT NULL auto_increment,
productID int,
name varchar(255),
street varchar(255),
city varchar(255),
state varchar(255),
zip int(8),
username varchar(255),
password varchar(255),
confirm_password varchar(255),
phone varchar(255),
PRIMARY KEY (ID),
);

CREATE TABLE Sales
(
ID int NOT NULL auto_increment,
customerID int,
amount float(15),
salequantity int(10),
PRIMARY KEY (ID),
FOREIGN KEY (customerID) REFERENCES Customers(ID)
);

CREATE TABLE Inventory
(
productID int,
saleID int,
FOREIGN KEY (productID) REFERENCES Products(ID),
FOREIGN KEY (saleID) REFERENCES Sales(ID)
);

这是最后的查询。只希望这个简单的工作。

UPDATE Sales  INNER JOIN Sales ON Inventory.saleID=Sales.ID SET salequantity=2 where Sales.customerID=1 AND Inventory.productID=1;

从第一条评论开始查询，但也失败了。

UPDATE Sales SET salequantity=5 FROM Sales JOIN Inventory ON Inventory.saleID=Sales.ID JOIN Customers ON Sales.customerID=Customers.ID JOIN Products ON Inventory.productID = Products.ID JOIN Categories ON Products.categoryID=Categories.ID where Customers.ID=1 AND Products.ID=1;

解决方案聚合函数完美运行

$customerProducts = mysql_query("select Products.ID, Products.name, Products.description, Products.quantity,Products.price, Categories.type, Sales.ID, Sales.customerID, Sales.amount, Sales.salequantity, Inventory.productID, Inventory.saleID, Customers.ID, Customers.productID, COUNT(Products.name) AS productsCount from(((( Inventory INNER JOIN Sales ON Inventory.saleID=Sales.ID) JOIN Customers ON Sales.customerID=Customers.ID) JOIN Products ON Inventory.productID = Products.ID) JOIN Categories ON Products.categoryID=Categories.ID) where Customers.ID ='$customersID' GROUP BY Products.name");

Answer 1

架构

create table sales (id varchar(10), customerid varchar(10), salequantity varchar(10))
create table Inventory (id varchar(10), productID varchar(10), saleID varchar(10))
create table Customers (id varchar(10))
create table Products (id varchar(10), categoryID varchar(10))
create table Categories (id varchar(10))

更正查询

UPDATE Sales SET salequantity='$takeCount' 
  FROM Sales JOIN Inventory ON Inventory.saleID=Sales.ID
             JOIN Customers ON Sales.customerID=Customers.ID
             JOIN Products ON Inventory.productID = Products.ID
             JOIN Categories ON Products.categoryID=Categories.ID
  where Customers.ID='$customersID' AND Products.ID='$id'

输出

(0 行受影响)

更改为查询

在第一个连接中，实际查询类似于“FROM Sales JOIN Sales ON Inventory.saleID=Sales.ID”，将其更正为“FROM Sales” JOIN Inventory ON Inventory.saleID=Sales.ID "。不清楚你想要的输出到底是什么。但上面的查询将解决编译错误。