Problems with sql update statement while using subquery

Question

I'm having 3 tables: PRICE, PRICE_PRODUCT, CHANGEABLE_PRICES. (I cannot change the data modeling in the database)

the issue is that I need to update sometimes table CHANGEABLE_PRICES based on PRICE table. I can link these tables via PRICE_PRODUCT table and date fields (Valid_date and date) which is present in both of them. I can link PRICE and PRICE_PRODUCT based on PriceID. and PRICE_PRODUCT and CHANGEABLE_PRICES based on ProductID,CompanyID,DeliveryID. Table CHANGEABLE_PRICES and PRICE I can link only via Valid_date and date.

 CREATE TABLE [dbo].[PRICE](
    [PriceId] [int] NOT NULL,
    [Valid_date] [datetime] NOT NULL,
    [Price_Value] [decimal](18, 0) NOT NULL
) ON [PRIMARY]
GO

INSERT INTO PRICE VALUES (21, '2018-05-31', 45)
INSERT INTO PRICE VALUES (22, '2018-05-06', 10)

CREATE TABLE [dbo].[PRICE_PRODUCT](
    [PriceID] [int] NOT NULL,
    [ProductID] [int] NOT NULL,
    [CompanyID] [nvarchar](50) NOT NULL,
    [DeliveryID] [nvarchar](50) NOT NULL
) ON [PRIMARY]
GO

INSERT INTO PRICE_PRODUCT VALUES (21, 1, 'A', '001')
INSERT INTO PRICE_PRODUCT VALUES (22, 1, 'A', '001')


CREATE TABLE [dbo].[CHANGEABLE_Prices](
    [ProductID] [int] NOT NULL,
    [CompanyID] [nvarchar](50) NOT NULL,
    [DeliveryID] [nvarchar](50) NOT NULL,
    [Date] [datetime] NOT NULL,
    [Price_Value] [decimal](18, 0) NOT NULL
) ON [PRIMARY]
GO

I was trying to do something like this:

update CHANGEABLE_Prices set Price_Value = (select p.Price_Value from Price p inner join PRICE_PRODUCT pp
on p.PriceId = pp.PriceId, CHANGEABLE_PRICES cp where cp.ProductID = CHANGABLE_Prices.ProductID
and cp.CompanyID = CHANGABLE_Prices.CompanyID 
and cp.DeliveryID = CHANGABLE_Prices.DeliveryID
and p.Valid_date = CHANGABLE_Prices.date)

But i'm getting error:

The multi-part identifier "CHANGABLE_Prices.ProductID" could not be bound.

and have no idea how to handle this issue.

any ideas? anyone, please?

thanks in advance.

`CHANGEABLE_PRICES cp ` your set alias `cp`, use CHANGABLE_Prices.ProductID -> cp.ProductID — Stanislav Kundii, Jul 19 '18 at 08:06

score 1 · Accepted Answer · answered Jul 19 '18 at 08:12

The CHANGEABLE_Prices table reference you're trying to update isn't in scope inside the subquery. You need to rewrite the UPDATE without the subquery, something like this:

update cp set Price_Value = p.Price_Value
from Price p
    inner join PRICE_PRODUCT pp on p.PriceId = pp.PriceId
    INNER JOIN CHANGEABLE_PRICES cp ON p.Valid_date = cp.date
        and cp.CompanyID = pp.CompanyID
        and cp.DeliveryID = pp.DeliveryID
        and p.Valid_date = p.date

Gordon Linoff · Answer 2 · 2018-07-20T12:28:00.263

Presumably, you intend:

update CHANGEABLE_Prices
    set Price_Value = (select p.Price_Value
                       from Price p inner join 
                            PRICE_PRODUCT pp
                            on p.PriceId = pp.PriceId
                       where cp.ProductID = CHANGABLE_Prices.ProductID and
                             cp.CompanyID = CHANGABLE_Prices.CompanyID and
                             p.DeliveryID = CHANGABLE_Prices.DeliveryID and
                             p.Valid_date = CHANGABLE_Prices.date
                       );

That is, you need correlation conditions to connect the subquery to the outer query.

You are probably better off using explain JOINs. However, the two methods are subtly different:

If there are multiple matches, then this will return an error. I consider this a benefit, that helps you avoid mistakes. The JOIN version will use an arbitrary matching value.
If there are no matches, then this assigns NULL. That may or may not be what you really want, but your attempted version would suggest that you want this behavior.

Thanks for helping me out with it but it does not work.. Msg 4104, Level 16, State 1, Line 58 The multi-part identifier "CHANGABLE_Prices.ProductID" could not be bound. Msg 4104, Level 16, State 1, Line 59 The multi-part identifier "CHANGABLE_Prices.CompanyID" could not be bound. ... — dropx, Jul 20 '18 at 05:20

Problems with sql update statement while using subquery

2 Answers2