CODEWITHSUNDEEP
sqlsql-server
bart-kosmala
bart-kosmala

Reputation: 981

Why is the column invalid in this SQL query?

I am trying to update the price of the products that were ordered by more than 10 customers. For that I use the Northwind sample database.

The relations between the required tables look like this:

[T]Orders       -> [K]CustomerID
                .. [K]OrderID

[T]OrderDetails -> [K]OrderID
                .. [K]ProductID

My solution as for now looks like this:

UPDATE Products
   SET UnitPrice = UnitPrice * 1.1
 WHERE EXISTS (SELECT COUNT(o.CustomerID) NofCust
                 FROM Orders o
           INNER JOIN [Order Details] od
                   ON o.OrderID = od.OrderID
                WHERE od.ProductID = ProductID
                      AND NofCust > 10
             GROUP BY od.ProductID);

I tried many different approaches, but there were problems with the select statement returning more than 1 row and so on, so this is the closest I got.

However the error I get in SQL Server is:

Msg 207, Level 16, State 1, Line 56
Invalid column name 'NofCust'.

When I try a different approach which in theory shouldn't return more than 1 row:

UPDATE Products
   SET UnitPrice = UnitPrice * 1.1
 WHERE (SELECT COUNT(o.CustomerID)
          FROM Orders o
    INNER JOIN [Order Details] od
            ON o.OrderID = od.OrderID
         WHERE od.ProductID = ProductID
      GROUP BY od.ProductID) > 10;

I get:

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

I assume that the second error may come from the fact that I can't name the table that I'm updating and can't explicitly say that I want, like:

ON o.OrderID = od.OrderID
         WHERE od.ProductID = <ProductsToUpdate>.ProductID
      GROUP BY od.ProductID) > 10;

How can I get this to work?

Upvotes: 0

Views: 64

Answers (2)

bart-kosmala
bart-kosmala

Reputation: 981

As @Sami suggested the answer would be:

UPDATE Products
   SET UnitPrice = UnitPrice * 1.1
 WHERE EXISTS (SELECT *
                 FROM Orders o
           INNER JOIN [Order Details] od
                   ON o.OrderID = od.OrderID
                WHERE od.ProductID = ProductID
             GROUP BY od.ProductID
               HAVING COUNT(DISTINCT o.CustomerID) > 10);

WHERE clause cannot be used with an aggregate function, plus it's not possible to use an alias as a value.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269613

I am trying to update the price of the products that were ordered by more than 10 customers.

I assume this means distinct customers.

For this, I think of a JOIN with GROUP BY more than EXISTS:

UPDATE p
   SET UnitPrice = UnitPrice * 1.1
   FROM Products p JOIN
        (SELECT od.ProductId
         FROM Orders o JOIN
              [Order Details] od
              ON o.OrderID = od.OrderID
         GROUP BY od.ProductId
         HAVING COUNT(DISTINCT o.CustomerId) > 10
        ) op
        ON op.ProductId = p.ProductId;

Upvotes: 1

Related Questions