Joining a table and updating values in a column in SQL (Postgres v13)-CodePudding

I am new to SQL and trying to manipulate some basic data. My schema is:

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');

I want to join the 'sales' and 'menu' tables, rename the price column to 'member_points' and apply an UPDATE to manipulate the data in that column to double the value if the 'product_name' is sushi. I have tried a lot of different methods but I keep getting errors where the Query doesn't recognize my UPDATE. Here is what I have worked out so far:

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';

I can't tell if my syntax is wrong, my references to the prior alias (menu_w_sales) is wrong, or if I am not placing my arguments correctly. Does anyone have an idea?

CodePudding user response：

I think the first problem is that you cant update a subquery.

On the other hand if you want to update a table be sure to use this syntax:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

In your code your are mixing a select and an update statement, that is why is throwing an error.

To solve your problem (or at least try to) i used a "CASE WHEN" expression and added a column called new_price so you could see how the expression works.

select menu_w_sales.*, 
case when menu_w_sales.product_name = 'sushi' and customer_id = 'A' then price*2
end as new_price
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

Here is the final code:

select menu_w_sales.*, 
case when menu_w_sales.product_name = 'sushi' and customer_id = 'A' then price*2 
else price
end as new_price
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

Pay attention to the "else" in the second code.

If you do not want to add an extra column i think the fastest way would be just to name the columns you want using menu_w_sales.columnName (basically everything except the price column)

Hope this is what you were looking for!

CodePudding user response：

You might fix the syntax due to the template

[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]
    SET { column_name = { expression | DEFAULT } |
          ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
          ( column_name [, ...] ) = ( sub-SELECT )
        } [, ...]
    [ FROM from_item [, ...] ]
    [ WHERE [JOIN | extra ] conditions | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

so an adequate one with a JOIN condition while getting rid of the subquery might be this for your case :

UPDATE dannys_diner.menu AS m
   SET price = price * 2 
  FROM dannys_diner.sales AS s
 WHERE product_name = 'sushi'
   AND customer_id = 'A'
   AND s.product_id = m.product_id

Demo