CODEWITHSUNDEEP
sqlsql-server
Samuel8000
Samuel8000

Reputation: 43

Update values in same table that reference different id

I have a table where prices for products are being stored for different outlets. Outlet A has a pepsi with itemprice 2 euro, Outlet B sells pepsi at 2.50 euro. Now I need to set all the item prices equal to each other so the 2 euros should become 2.50.

I tried the following: 

update PoscardOutlet_Default
set Poscard_Outlet_ItemPrice = (select Poscard_Outlet_ItemPrice
                                from PoscardOutlet_Default
                                where Poscard_Outlet_OutletPointer = 1)
where Poscard_Outlet_OutletPointer = 2

but I get this error:

Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.

I want all the items in Outlet 2 to have the same item price like in outlet 1.

Upvotes: 0

Views: 381

Answers (4)

Jaikrishnan
Jaikrishnan

Reputation: 33

This will work without CTE

update T1 set T1.Poscard_Outlet_ItemPrice = T2.Poscard_Outlet_ItemPrice FROM PoscardOutlet_Default t1 INNER JOIN PoscardOutlet_Default t2 ON t1.ItemId = t2.ItemId where T1.Poscard_Outlet_OutletPointer = 2 AND T2.Poscard_Outlet_OutletPointer = 1

Upvotes: 0

Richard Hansell
Richard Hansell

Reputation: 5403

Imagine that you have two outlets, and only two products:

Outlet   Product     Price
1        Sweets      50p
1        Drinks      60p
2        Sweets      40p
2        Drinks      55p

As things stand your query reads "set all the prices for outlet 1 to all the prices for outlet 2"... so let's start doing that manually:

  • the first product for Outlet 1 is "Sweets", so what price should we set it to?
  • the answer is "all the prices for Outlet 2" so that's 40p AND 55p;
  • but that's impossible, how can we set a single price to two different values?

That's basically what that error means Subquery returned more than one value.

What you should do instead is to only pick the price for "Sweets" from Outlet 2, and then when you move onto Drinks only pick the price for "Drinks" from Outlet 2, etc.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

I would use an updatable CTE here:

WITH cte AS (
    SELECT
        t1.Poscard_Outlet_ItemPrice AS ItemPriceOld,
        t2.Poscard_Outlet_ItemPrice AS ItemPriceNew
    FROM PoscardOutlet_Default t1
    INNER JOIN PoscardOutlet_Default t2
        ON t1.ItemId = t2.ItemId
    WHERE
        t1.Poscard_Outlet_OutletPointer = 2 AND
        t2.Poscard_Outlet_OutletPointer = 1
)

UPDATE cte
SET ItemPriceOld = ItemPriceNew;

I assume here that the table has a primary key column called ItemId, without which we can't really write any query.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269493

Now I need to set all the item prices equal to eachother so the 2 euros should become 2.50.

This question has little to do with your code. But to answer this question, use window functions and an updatable CTE:

with toupdate as (
      select pod.*,
             max(Poscard_Outlet_ItemPrice) over (partition by product) as max_price
      from PoscardOutlet_Default
update toupdate
    set Poscard_Outlet_ItemPrice = maxprice
    where Poscard_Outlet_ItemPrice <> maxprice or Poscard_Outlet_ItemPrice is null;

You can of course add appropriate filtering, if you don't want to update all products (although that is what you appear to be asking for).

Upvotes: 1

Related Questions