February 10, 2020 at 9:57 pm
For a query that consists of two or more tables you can use UPDATE but not INSERT INTO or DELETE (please correct me if I'm wrong). So I am testing this with CTEs.
I created two CTEs and joined them successfully. Now I am trying to use UPDATE on the two CTEs that were joined. I am getting the following error: "Msg 4104, Level 16, State 1, Line 151. The multi-part identifier "o.Description" could not be bound."
Here is my code:
WITH ProductTable(ProductID, Description)
AS
(
SELECT ProductID, ProductName
FROM dbo.Products
),
Orders(ProductID, Description) -- more than one CTEs created using one WITH statement
AS
(
SELECT ProductID, Product
FROM dbo.Orders
)
UPDATE Orders
SET o.Description = p.Description
FROM Orders o
INNER JOIN ProductTable p
ON o.ProductID = p.ProductID
WHERE o.productID = 56
What am I doing wrong? Or could it be that it is not possible to use UPDATE on two CTEs that have been joined?
February 10, 2020 at 10:06 pm
In the FROM clause of the UPDATE statement you list the table 'Orders'. My guess is you intend for this to be the CTE but if the schema is not specified it defaults to 'dbo'. A naming convention many adhere to is to append '_cte' to the end of CTE definitions.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
February 10, 2020 at 10:37 pm
I don't think that's the issue (not exactly), I think you are telling it to update on the aliased table. Try taking the alias off, just say UPDATE Orders SET Description = p.Description.
BUT, to scdecade's point, you have a table and a CTE named the same thing. in your UPDATE, use the schema.tablename for sure. Plus, maybe don't name CTEs the same thing as tables anyway?
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
February 10, 2020 at 10:43 pm
You're UPDATE statement is incorrect. If you are going to alias something, you need to use that alias throughout.
UPDATE o -- use the alias here.
SET o.Description = p.Description
FROM Orders o
INNER JOIN ProductTable p
ON o.ProductID = p.ProductID
WHERE o.productID = 56
Also, you are using the same name for the CTE as for the underlying table. I would recommend avoiding that.
You can use INSERT INTO with any SELECT statement that produces a result set with unique non-null column names.
You can DELETE from joined tables under certain conditions. Usually it's best to use an EXISTS/NOT EXISTS instead.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 10, 2020 at 10:45 pm
May I ask why you are using CTEs in this manner when you could just use the tables themselves?
February 11, 2020 at 3:13 am
May I ask why you are using CTEs in this manner when you could just use the tables themselves?
That was going to be my exact question.... or is this just an experiment to see how CTEs work in an UPDATE.
--Jeff Moden
February 11, 2020 at 3:16 am
You're
UPDATEstatement is incorrect. If you are going to alias something, you need to use that alias throughout.UPDATE o -- use the alias here.
SET o.Description = p.Description
FROM Orders o
INNER JOIN ProductTable p
ON o.ProductID = p.ProductID
WHERE o.productID = 56Also, you are using the same name for the CTE as for the underlying table. I would recommend avoiding that.
You can use
INSERT INTOwith anySELECTstatement that produces a result set with unique non-null column names.You can
DELETEfrom joined tables under certain conditions. Usually it's best to use anEXISTS/NOT EXISTSinstead.Drew
All,
I took many suggestions. I added 'CTE' to the end of the CTE name. It looks like the table name after UPDATE and SET must be aliased or non-aliased. Either way, the table name after FROM can be aliased.
I tried to prefix the table names with the schema 'dbo' and I couldn't get it to work. Not sure why.
Thank you everybody for you help with this.
WITH ProductTableCTE(ProductID, Description)
AS
(
SELECT ProductID, ProductName
FROM dbo.Products
),
OrdersCTE(ProductID, Description) -- more than one CTEs created using one WITH statement
AS
(
SELECT ProductID, Product
FROM dbo.Orders
)
UPDATE OrdersCTE
SET OrdersCTE.Description = p.Description -- The multi-part identifier "o.Description" could not be bound.
FROM OrdersCTE o -- Both UPDATE and SET must be aliased or non-aliased. Eitehr way FROM can still have an alias
INNER JOIN ProductTableCTE p
ON o.ProductID = p.ProductID
WHERE o.productID = 56
February 11, 2020 at 4:03 am
February 11, 2020 at 8:37 pm
I think your approach is wrong. A name and a description are two distinctly different kinds of attributes; which one are you using in regard to products? Using a CTE to change a column from one attribute property to another is as bad as making a color into a shoe size.
Including metadata in a data element name is a design flaw so bad, ot is called a "tibble" in SQL slang. Products can be identified by anyone of several industry standards, but I prefer the GTIN (global trade item number). I hope you are aware that CTE table declarations are executed in the order in which they appear in the CTE.
Cleaning up your code a little bit:
WITH Products(product_gtin, product_name)
AS
(SELECT product_gtin, product_name -- or description??
FROM Products),
Orders(product_gtin, product_description)
AS
(SELECT product_gtin, product_description
FROM Orders)
...
You need to Google problems with the old Sybase UPDATE..FROM.. syntax; it does not work! It has a fundamental cardinality error so that it uses the last table to physically appear in storage in the from clause. This can change depending on indexing, re-compiles or the optimization. It is by its nature, unpredictable. Please Google it. This is why we added the MERGE statement and tried to come up to ANSI/ISO standards
From here on out everything you're doing is pretty much wrong. Aside from using an old, dangerous syntax, you mess up the aliases. The product name turn in the product description, aliases were created in the from clause, etc. I think you're trying to change the product name in the Orders to the product name in the products table. Or was it the product description? Unfortunately, all you've done is de-normalize the schema and increase redundancy.
CREATE TABLE Products
(product_gtin CHAR(15) NOT NULL PRIMARY KEY,
product_name CHAR(25)
product_description VARCHAR(100) NOT NULL,
...)
Please note that the product table has the attributes of a product, they are not split out into other tables.
CREATE TABLE Orders
(order_nbr CHAR(10) NOT NULL PRIMARY KEY,
product_gtin CHAR(15) NOT NULL
REFERENCES Products(grin) ON DELETE CASCADE,
..);
Please note the Orders table as a REFERENCES clause back to the products that are on each order. The attempts to update Orders is the way we would have done it in the 1950s on punch cards. You are repeating the product information over and over and over. You think that you have to physically materialize redundant data. A SQL programmer would've written something like this:
SELECT O.order_nbr, O.gtin, P_product_description
FROM Orders AS O, Products AS P
WHERE O.gtin = P.gtin
AND O.product_gtin = '56';
If you're looking for an example of how to use CTE constructs, there are much better ways of doing it.
Please post DDL and follow ANSI/ISO standards when asking for help.
February 11, 2020 at 8:54 pm
I have never heard of a "tibble", so I googled it. Nothing. Not sure how widespread that term is.
I also tried to google the "problems with old Sybase UPDATE..FROM...WHERE syntax", and had trouble. Did find this though, from MS documentation about the MERGE statement:
"Performance Tip: The conditional behavior described for the MERGE statement works best when the two tables have a complex mixture of matching characteristics. For example, inserting a row if it doesn't exist, or updating a row if it matches. When simply updating one table based on the rows of another table, improve the performance and scalability with basic INSERT, UPDATE, and DELETE statements."
https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver15
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
February 11, 2020 at 10:03 pm
>> I have never heard of a "tibble", so I googled it. Nothing. Not sure how widespread that term is. <<
The term was coined by Phil Factor and it even has a few little poems and songs that use it. Look at some of his columns over on Redgate. It originally referred to the use of the affix "tbl_" on table names, but it also refers to the Volkswagen "vw_" affix on the names of views. It violates a fundamental design principle, that you do not mix data and metadata. A data element name should describe exactly what the data element is by its nature,, without regard to its data type, location in the schema, storage method, etc.
>> I also tried to google the "problems with old Sybase UPDATE..FROM...WHERE syntax", and had trouble. Did find this though, from MS documentation about the MERGE statement: <<
That used to be a standard posting on SQL Server forms, so you should be able to find it. When the MERGE statement has two or more rows that qualify, you get a cardinality violation. The old Sybase update would simply use the table reference that was physically last in storage.
>> " ... When simply updating one table based on the rows of another table, improve the performance and scalability with basic INSERT, UPDATE, and DELETE statements." <<
The real problem is that Microsoft did a bad job of implementing the merge statement. You will not find the same problem in DB2, Postgres, etc I'm going to assume that Microsoft will catch up in a release or two.
15.04. A Note on Flaws in a Common Vendor Extension
While I do not like to spend much time discussing non-standard SQL-like languages, the T-SQL language from Sybase and Microsoft had a horrible flaw in it that users need to be warned about. They have a proprietary syntax that allows a FROM clause in the UPDATE statement.
Neither works correctly. The original Sybase statement would do multiple updates of the target table when the source table was in a many-to-one relationship with the base table being updated.
UPDATE T1
SET T1.x = 2 * T1.x
FROM T2
WHERE T1.x = T2.x;
The column T1.x will be doulbed and re-doubled for each x in T2.
The Microsoft version solved the cardinality problem by simply grabbing one of the values based on the current physical arrangement of the rows in the table. This is a simple example from Adam Mechanic:
CREATE TABLE Foo
(col_a CHAR(1) NOT NULL,
col_b INTEGER NOT NULL);
INSERT INTO Foo VALUES ('A', 0), ('B', 0), ('C', 0);
CREATE TABLE Bar
(col_a CHAR(1) NOT NULL,
col_b INTEGER NOT NULL);
INSERT INTO Bar
VALUES ('A', 1), ('A', 2), ('B', 1), ('C', 1);
You run this proprietary UPDATE with a FROM clause:
UPDATE Foo
SET Foo.col_b = Bar.col_b
FROM Foo INNER JOIN Bar
ON Foo.col_a = Bar.col_a;
The result of the UPDATE cannot be determined. The value of the column will depend upon either order of insertion, (if there are no clustered indexes present), or on order of clustering (but only if the cluster is not fragmented).
Please post DDL and follow ANSI/ISO standards when asking for help.
February 11, 2020 at 10:24 pm
….aaand now I'm wondering what I've screwed up during my tenure. Thanks Joe!
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
February 12, 2020 at 11:08 am
drew.allen wrote:You're
UPDATEstatement is incorrect. If you are going to alias something, you need to use that alias throughout.UPDATE o -- use the alias here.
SET o.Description = p.Description
FROM Orders o
INNER JOIN ProductTable p
ON o.ProductID = p.ProductID
WHERE o.productID = 56Also, you are using the same name for the CTE as for the underlying table. I would recommend avoiding that.
You can use
INSERT INTOwith anySELECTstatement that produces a result set with unique non-null column names.You can
DELETEfrom joined tables under certain conditions. Usually it's best to use anEXISTS/NOT EXISTSinstead.Drew
All,
I took many suggestions. I added 'CTE' to the end of the CTE name. It looks like the table name after UPDATE and SET must be aliased or non-aliased. Either way, the table name after FROM can be aliased.
I tried to prefix the table names with the schema 'dbo' and I couldn't get it to work. Not sure why.
Thank you everybody for you help with this.
WITH ProductTableCTE(ProductID, Description)
AS
(
SELECT ProductID, ProductName
FROM dbo.Products
),
OrdersCTE(ProductID, Description) -- more than one CTEs created using one WITH statement
AS
(
SELECT ProductID, Product
FROM dbo.Orders
)
UPDATE OrdersCTE
SET OrdersCTE.Description = p.Description -- The multi-part identifier "o.Description" could not be bound.
FROM OrdersCTE o -- Both UPDATE and SET must be aliased or non-aliased. Eitehr way FROM can still have an alias
INNER JOIN ProductTableCTE p
ON o.ProductID = p.ProductID
WHERE o.productID = 56
What you're trying to do, does actually work. Had you followed all of the corrections offered by contributors, you would have arrived at this:
IF OBJECT_ID('tempdb..#Products') IS NOT NULL DROP TABLE #Products
CREATE TABLE #Products (ProductID INT IDENTITY(1,1), ProductName VARCHAR(20))
INSERT INTO #Products (ProductName) VALUES ('ProductOne'), ('ProductTwo'), ('ProductThree')
IF OBJECT_ID('tempdb..#Orders') IS NOT NULL DROP TABLE #Orders
CREATE TABLE #Orders (OrderID INT IDENTITY(1,1), OrderDescription VARCHAR(20), ProductID INT)
INSERT INTO #Orders (OrderDescription, ProductID) VALUES ('OrderOne',2), ('OrderTwo',3), ('OrderThree',1)
SELECT * FROM #Orders;
WITH ProductTableCTE(ProductID, Description)AS (SELECT ProductID, ProductName
FROM #Products),
OrdersCTE(ProductID, Description)AS (SELECT ProductID, OrderDescription
FROM #Orders)
UPDATE o
SET [Description] = p.[Description]
FROM OrdersCTE o
INNER JOIN ProductTableCTE p
ON o.ProductID = p.ProductID
WHERE o.productID = 3
SELECT * FROM #Orders
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 12, 2020 at 2:03 pm
Still curious as to WHY you were using CTEs to accomplish your task. It was just as easily done using the tables themselves. Compare the code below.
IF OBJECT_ID('tempdb..#Products') IS NOT NULL
DROP TABLE [#Products];
CREATE TABLE [#Products]
(
[ProductID] int IDENTITY(1, 1)
, [ProductName] varchar(20)
);
INSERT INTO [#Products](
[ProductName]
)
VALUES
('ProductOne')
, ('ProductTwo')
, ('ProductThree');
IF OBJECT_ID('tempdb..#Orders') IS NOT NULL
DROP TABLE [#Orders];
CREATE TABLE [#Orders](
[OrderID] int IDENTITY(1, 1)
, [OrderDescription] varchar(20)
, [ProductID] int
);
INSERT INTO [#Orders]
(
[OrderDescription]
, [ProductID]
)
VALUES
('OrderOne', 2)
, ('OrderTwo', 3)
, ('OrderThree', 1);
SELECT [OrderID], [OrderDescription], [ProductID] FROM [#Orders];
WITH [ProductTableCTE]
([ProductID], [Description])
AS
(
SELECT [ProductID], [ProductName] FROM [#Products]
)
, [OrdersCTE]
([ProductID], [Description])
AS
(
SELECT [ProductID], [OrderDescription] FROM [#Orders]
)
UPDATE
[o]
SET
[o].[Description] = [p].[Description]
FROM
[OrdersCTE] AS [o]
INNER JOIN [ProductTableCTE] AS [p]
ON [o].[ProductID] = [p].[ProductID]
WHERE
[o].[ProductID] = 3;
SELECT [OrderID], [OrderDescription], [ProductID] FROM [#Orders];
GO
IF OBJECT_ID('tempdb..#Products') IS NOT NULL
DROP TABLE [#Products];
CREATE TABLE [#Products]
(
[ProductID] int IDENTITY(1, 1)
, [ProductName] varchar(20)
);
INSERT INTO [#Products](
[ProductName]
)
VALUES
('ProductOne')
, ('ProductTwo')
, ('ProductThree');
IF OBJECT_ID('tempdb..#Orders') IS NOT NULL
DROP TABLE [#Orders];
CREATE TABLE [#Orders](
[OrderID] int IDENTITY(1, 1)
, [OrderDescription] varchar(20)
, [ProductID] int
);
INSERT INTO [#Orders]
(
[OrderDescription]
, [ProductID]
)
VALUES
('OrderOne', 2)
, ('OrderTwo', 3)
, ('OrderThree', 1);
SELECT [OrderID], [OrderDescription], [ProductID] FROM [#Orders];
UPDATE
[o]
SET
[o].[OrderDescription] = [p].[ProductName]
FROM
[#Orders] AS [o]
INNER JOIN [#Products] AS [p]
ON [o].[ProductID] = [p].[ProductID]
WHERE
[o].[ProductID] = 3;
SELECT [OrderID], [OrderDescription], [ProductID] FROM [#Orders];
GO
February 12, 2020 at 11:33 pm
Jeff Moden wrote:Lynn Pettis wrote:May I ask why you are using CTEs in this manner when you could just use the tables themselves?
That was going to be my exact question.... or is this just an experiment to see how CTEs work in an UPDATE.
And we did not even get an answer to the question.
Since I am new to CTEs (and SQL) I just wanted to do a CTE to see how it works. Because of this, it looks like I inadvertantly made a lot of mistakes. So this was just an experiment to see if I can create a CTE and get an idea of how they work. Therefore my example doesn't have any practical use.
Lynn,
I thought that a CTE definitions would always refer to some underlying table.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply