CODEWITHSUNDEEP
sqlsql-server
dropx
dropx

Reputation: 25

Problems with sql update statement while using subquery

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.

Upvotes: 1

Views: 296

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

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.

Upvotes: 0

cf_en
cf_en

Reputation: 1661

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

Upvotes: 1

Related Questions