mysql - 更新表错误代码1054

Question

我想用我选择的新值更新表 ospos_sale 值，但无法更新。

这是我的sql代码

更新 ospos_sales set subtotal_amount = ( select * from (select sale.subtotal_amount-saler.subtotal_amount
from ospos_sales sale left join ospos_salesreturn saler on sale.id = saler.fk_sales_id where sale.id=ospos_sales.id) as m2)，total_discount = (select * from (select sale.total_discount-saler.total_discount from ospos_sales sale left join ospos_salesreturn saler在 sale.id = saler.fk_sales_id (其中 sale.id=ospos_sales.id) as m3) 上，total_amount = (select * from (select sale.total_amount-saler.total_amount
from ospos_sales sale left join ospos_salesreturn saler on sale.id = saler.fk_sales_id where sale.id=ospos_sales.id) as m4),change_amount = (select * from (select sale.paid_amount - sale.total_amount+saler.total_amount from ospos_sales sale left join ospos_salesreturn saler on sale.id = saler.fk_sales_id where sale.id=ospos_sales.id) as m4) where ospos_sales.id= 10003

这是我的表结构

    CREATE TABLE `ospos_sales`  (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `saletime` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `fk_customers_id` int(10) NULL DEFAULT 0,
  `fk_users_id` int(10) NOT NULL DEFAULT 0,
  `fk_locations_id` int(255) NOT NULL,
  `comment` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `invoicenumber` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `quotenumber` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `totalsaledquantity` decimal(15, 3) NOT NULL,
  `subtotal_amount` decimal(25, 0) NOT NULL,
  `fk_dinnertables_id` int(11) NULL DEFAULT NULL,
  `total_discount` decimal(25, 0) NOT NULL,
  `coupon_amount` decimal(25, 0) NOT NULL,
  `tax_amount` decimal(25, 0) NOT NULL,
  `round_amount` decimal(25, 0) NOT NULL,
  `total_amount` decimal(25, 0) NOT NULL,
  `paid_amount` decimal(25, 0) NOT NULL,
  `change_amount` decimal(25, 0) NOT NULL,
  `salestatus` enum('draft','open','sale','return_request','returned','cancel_request','cancelled','cleared') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'draft',
  `paymentstatus` enum('unpaid','paid','partially','return') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'unpaid',
.
.
.
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `invoice_number`(`invoicenumber`) USING BTREE,
  INDEX `customer_id`(`fk_customers_id`) USING BTREE,
  INDEX `employee_id`(`fk_users_id`) USING BTREE,
  INDEX `sale_time`(`saletime`) USING BTREE,
  INDEX `dinner_table_id`(`fk_dinnertables_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 36096 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
SET FOREIGN_KEY_CHECKS = 1;

表 SaleReturnitem 是

CREATE TABLE `ospos_salesreturn`  (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `returntime` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `fk_customers_id` int(10) NULL DEFAULT NULL,
  `fk_locations_id` int(255) NOT NULL,
  `fk_sales_id` int(255) NOT NULL,
  `returnref` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `comment` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `subtotal_amount` decimal(25, 0) NOT NULL,
  `total_discount` decimal(25, 0) NOT NULL,
  `coupon_amount` decimal(25, 0) NOT NULL,
  `tax_amount` decimal(25, 0) NOT NULL,
  `total_amount` decimal(25, 0) NOT NULL,
  `paid_amount` decimal(25, 0) NOT NULL,
  `change_amount` decimal(25, 0) NOT NULL,
  `returnstatus` enum('draft','returned') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'draft',
  `fk_users_id` int(10) NOT NULL DEFAULT 0,
  `status` enum('0','1') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '1',
  `dels` enum('1','0') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0',
  `cdate` int(15) NOT NULL,
  `mdate` int(15) NOT NULL,
  `syncfrom` int(11) NOT NULL DEFAULT 0,
  `syncmasterid` int(255) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `customer_id`(`fk_customers_id`) USING BTREE,
  INDEX `employee_id`(`fk_users_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 10 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

错误消息是:

> 1054 - Unknown column 'ospos_sales.id' in 'where clause' 

请帮忙。

Answer 1

您的子查询为 ospos_sales 提供了一个别名。当你给一个表一个别名时，你不能再使用表名来引用它，但你必须使用该别名。

我假设您的目标是让它引用更新表，但由于有 2 个级别，它看不到它。

最简单的解决方案是简单地将 ID 放入子查询中，而不是引用列。否则，您需要重新构造查询。

如果我没有错过任何事情，那就是这样的。

update ospos_sales 
set 
    subtotal_amount = (select 
            *
        from
            (select 
                sale.subtotal_amount - saler.subtotal_amount
            from
                ospos_sales sale
            left join ospos_salesreturn saler ON sale.id = saler.fk_sales_id
            where
                sale.id = 10003) as m2),
    total_discount = (select 
            *
        from
            (select 
                sale.total_discount - saler.total_discount
            from
                ospos_sales sale
            left join ospos_salesreturn saler ON sale.id = saler.fk_sales_id
            where
                sale.id = 10003) as m3),
    total_amount = (select 
            *
        from
            (select 
                sale.total_amount - saler.total_amount
            from
                ospos_sales sale
            left join ospos_salesreturn saler ON sale.id = saler.fk_sales_id
            where
                sale.id = 10003) as m4),
    change_amount = (select 
            *
        from
            (select 
                sale.paid_amount - sale.total_amount + saler.total_amount
            from
                ospos_sales sale
            left join ospos_salesreturn saler ON sale.id = saler.fk_sales_id
            where
                sale.id = 10003) as m4)
where
    ospos_sales.id = 10003;