我是SQL的新手,正在try 操作一些基本数据.我的方案是:
CREATE SCHEMA dannys_diner;
SET search_path = dannys_diner;
CREATE TABLE sales (
"customer_id" VARCHAR(1),
"order_date" DATE,
"product_id" INTEGER
);
INSERT INTO sales
("customer_id", "order_date", "product_id")
VALUES
('A', '2021-01-01', '1'),
('A', '2021-01-01', '2'),
('A', '2021-01-07', '2'),
('A', '2021-01-10', '3'),
('A', '2021-01-11', '3'),
('A', '2021-01-11', '3'),
('B', '2021-01-01', '2'),
('B', '2021-01-02', '2'),
('B', '2021-01-04', '1'),
('B', '2021-01-11', '1'),
('B', '2021-01-16', '3'),
('B', '2021-02-01', '3'),
('C', '2021-01-01', '3'),
('C', '2021-01-01', '3'),
('C', '2021-01-07', '3');
CREATE TABLE menu (
"product_id" INTEGER,
"product_name" VARCHAR(5),
"price" INTEGER
);
INSERT INTO menu
("product_id", "product_name", "price")
VALUES
('1', 'sushi', '10'),
('2', 'curry', '15'),
('3', 'ramen', '12');
CREATE TABLE members (
"customer_id" VARCHAR(1),
"join_date" DATE
);
INSERT INTO members
("customer_id", "join_date")
VALUES
('A', '2021-01-07'),
('B', '2021-01-09');
我想联接‘Sales’和‘Menu’表,将价格列重命名为‘Members_Points’,并应用更新操作该列中的数据,以便在‘product_name’为sushi时将值加倍.我try 了很多不同的方法,但总是遇到查询无法识别我的更新的错误.以下是我到目前为止得出的结论:
SELECT *
FROM
(
SELECT *
FROM dannys_diner.sales
INNER JOIN dannys_diner.menu
ON dannys_diner.sales.product_id = dannys_diner.menu.product_id) AS menu_w_sales
UPDATE menu_w_sales
SET price = (price * 2)
WHERE product_name = sushi
AND customer_id = 'A';
我不知道是我的语法错误、我对先前别名(Menu_W_Sales)的引用是错误的,还是我没有正确地放置参数.有谁有主意吗?