update query

hillelben
hillelben used Ask the Experts™
on
i ran an update query (bellow) that only updated a bout half the products in the database .  I was trying to figure out why only some products were getting updated and ran the following query which listed all the products in the database.
select *
FROM Products_Extended pe LEFT JOIN Products p ON pe.ProductID=p.ProductID LEFT JOIN (SELECT * FROM (
SELECT Categories_Products_Link.*, Categories.CategoryName, Categories.ParentID, row_number() over ( partition by Categories_Products_Link.ID ORDER BY Categories_Products_Link.CategoryID) rnum
FROM Categories_Products_Link,Categories
WHERE Categories.CategoryID=Categories_Products_Link.CategoryID ) temp
WHERE rnum = 1 ) as cp ON cp.ID=pe.ProductID LEFT JOIN Categories ON cp.ParentID=Categories.CategoryID

Any ideas why the update query isn't updating everything and how to fix this?
UPDATE Products_Extended
SET METATAG_Title = p.ProductName+' is made by ' + pe.ProductManufacturer + ' and is listed in our ' +Categories.CategoryName + ' and ' + cp.CategoryName
FROM Products_Extended pe LEFT JOIN Products p ON pe.ProductID=p.ProductID LEFT JOIN (SELECT * FROM (
SELECT Categories_Products_Link.*, Categories.CategoryName, Categories.ParentID, row_number() over ( partition by Categories_Products_Link.ID ORDER BY Categories_Products_Link.CategoryID) rnum
                                FROM Categories_Products_Link,Categories
                                WHERE Categories.CategoryID=Categories_Products_Link.CategoryID ) temp
WHERE rnum = 1 ) as cp ON cp.ID=pe.ProductID LEFT JOIN Categories ON cp.ParentID=Categories.CategoryID

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
One possibility is if one of the items in
  P.PRODUCTNAME+' IS MADE BY ' + PE.PRODUCTMANUFACTURER + ' AND
      IS LISTED IN OUR ' +CATEGORIES.CATEGORYNAME + ' AND
      ' + CP.CATEGORYNAME
is Null than the whole string will be Null. Ty using & instead:
   P.PRODUCTNAME & ' IS MADE BY ' & PE.PRODUCTMANUFACTURER & ' AND
      IS LISTED IN OUR ' & CATEGORIES.CATEGORYNAME & ' AND
      ' & CP.CATEGORYNAME
It converts Nulls to ""

Is
WHERE RNUM = 1
restricting the update?

If the above two suggestions don't help, What do you get for the records that do not update? Blank? No change?  

Author

Commented:
didn't work with the & got the message. The data types varchar and varchar are incompatible in the boolean AND operator.
the rnum=1 is limiting the Categories_Products_Link results because we only want one even though there are many for each product.  but even so with the select query every product was listed
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

<The data types varchar and varchar are incompatible in the boolean AND operator.?
That is telling you that one or more of your objects is a variant so try this:
   CStr(P.PRODUCTNAME) & ' IS MADE BY ' & CStr(PE.PRODUCTMANUFACTURER) & ' AND
      IS LISTED IN OUR ' & CStr(CATEGORIES.CATEGORYNAME) & ' AND
      ' & CStr(CP.CATEGORYNAME)

Author

Commented:
got the error CStr is not a recognized built-in function name
heres the query so you can see if i made any mistakes bellow
UPDATE Products_Extended
SET METATAG_Title = CStr(p.ProductName) & ' is made by ' & CStr(pe.ProductManufacturer) & ' and is listed in our ' & CStr(Categories.CategoryName) & ' and ' & CStr(cp.CategoryName)
FROM Products_Extended pe LEFT JOIN Products p ON pe.ProductID=p.ProductID LEFT JOIN (SELECT * FROM (
SELECT Categories_Products_Link.*, Categories.CategoryName, Categories.ParentID, row_number() over ( partition by Categories_Products_Link.ID ORDER BY Categories_Products_Link.CategoryID) rnum
                                FROM Categories_Products_Link,Categories
                                WHERE Categories.CategoryID=Categories_Products_Link.CategoryID ) temp
WHERE rnum = 1 ) as cp ON cp.ID=pe.ProductID LEFT JOIN Categories ON cp.ParentID=Categories.CategoryID

Open in new window

Top Expert 2012

Commented:
For the record, the author is using T- SQL (SQL Server 2005) and not the SQL dialect used with MS Access.  So you cannot use & for concatenating strings, nor does the CStr() function exist in T-SQL.
acperkins,

Ah, thanks. Missed that.

So, hillelben, back to the question:
What do you get for the records that do not update? Blank? No change?    
Top Expert 2012

Commented:
>>i ran an update query (bellow) that only updated a bout half the products in the database <<
Just to be clear, but you are updating the Products_Extended table and not the Products table.  That means that while all the Products may be included in the query, perhaps only half the Products_Extended table is present.

Perhaps if you were to elaborate a tad more in the details as to the relationship between Products and Products_Extended tables it would help.  In particular, is it a one to one or one to many relationship?  Do you have a constraint enforcing this relationship between these two table? If not are there Products_Extended that do not have matching Products?  If you do have a relationship why do you have a LEFT JOIN?
Mohed SharfiVice CEO

Commented:
Hi
How are you, please try to Execute this:
thanks
UPDATE Products_Extended
SET METATAG_Title = cast(p.ProductName AS varchar)  & ' is made by ' & cast(pe.ProductManufacturer  AS varchar) & 
' and is listed in our ' & 
cast(Categories.CategoryName AS varchar) & ' and ' & cast(cp.CategoryName  AS varchar)
FROM Products_Extended pe LEFT JOIN Products p 
ON pe.ProductID=p.ProductID LEFT JOIN
 (SELECT * FROM (SELECT Categories_Products_Link.*, Categories.CategoryName,
  Categories.ParentID, row_number() over ( partition by Categories_Products_Link.ID 
                 ORDER BY Categories_Products_Link.CategoryID) rnum
                                FROM Categories_Products_Link,Categories
                                WHERE Categories.CategoryID=Categories_Products_Link.CategoryID ) temp
WHERE rnum = 1 ) as cp ON cp.ID=pe.ProductID LEFT JOIN Categories ON cp.ParentID=Categories.CategoryID

Open in new window

Author

Commented:
in answer to acperkins:
the Products and Products_Extended tables have a 1 to 1 relationship there should be an entry in the Products_Extended table for each product. its true there realy no reason there to use a left join this is a continuation of my question http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_24571390.html which never really got resolved
Top Expert 2012

Commented:
Then verify that all the products are included with this query:
SELECT  *
FROM    Products_Extended pe
        INNER JOIN Products p ON pe.ProductID = p.ProductID
        LEFT JOIN ( SELECT  *
                    FROM    ( SELECT    Categories_Products_Link.*,
                                        Categories.CategoryName,
                                        Categories.ParentID,
                                        ROW_NUMBER() OVER ( PARTITION BY Categories_Products_Link.ID ORDER BY Categories_Products_Link.CategoryID ) rnum
                              FROM      Categories_Products_Link,
                                        Categories
                              WHERE     Categories.CategoryID = Categories_Products_Link.CategoryID
                            ) temp
                    WHERE   rnum = 1
                  ) AS cp ON cp.ID = pe.ProductID
        LEFT JOIN Categories ON cp.ParentID = Categories.CategoryID

What about this one:
SELECT  *
FROM    Products_Extended pe
        INNER JOIN Products p ON pe.ProductID = p.ProductID
        INNER JOIN ( SELECT  *
                    FROM    ( SELECT    Categories_Products_Link.*,
                                        Categories.CategoryName,
                                        Categories.ParentID,
                                        ROW_NUMBER() OVER ( PARTITION BY Categories_Products_Link.ID ORDER BY Categories_Products_Link.CategoryID ) rnum
                              FROM      Categories_Products_Link,
                                        Categories
                              WHERE     Categories.CategoryID = Categories_Products_Link.CategoryID
                            ) temp
                    WHERE   rnum = 1
                  ) AS cp ON cp.ID = pe.ProductID
        INNER JOIN Categories ON cp.ParentID = Categories.CategoryID

Author

Commented:
its the same results as with the left join - you get all the products with that query

Author

Commented:
really when i said there were records that weren't getting updated... they are  - they are getting set to blank
Top Expert 2012

Commented:
>>its the same results as with the left join - you get all the products with that query<<
The first query or the second.  This is important.


>>really when i said there were records that weren't getting updated... they are  - they are getting set to blank<<
So they are getting updated, it is just that they are getting updated with a blank? or is it Null?

What happens if you do this:

SELECT  p.ProductName +
            ' is made by ' + pe.ProductManufacturer
        + ' and is listed in our ' + Categories.CategoryName +
        ' and ' + cp.CategoryName
FROM    Products_Extended pe
        INNER JOIN Products p ON pe.ProductID = p.ProductID
        INNER JOIN ( SELECT *
                     FROM   ( SELECT    Categories_Products_Link.*,
                                        Categories.CategoryName,
                                        Categories.ParentID,
                                        ROW_NUMBER() OVER ( PARTITION BY Categories_Products_Link.ID ORDER BY Categories_Products_Link.CategoryID ) rnum
                              FROM      Categories_Products_Link,
                                        Categories
                              WHERE     Categories.CategoryID = Categories_Products_Link.CategoryID
                            ) temp
                     WHERE  rnum = 1
                   ) AS cp ON cp.ID = pe.ProductID
        INNER JOIN Categories ON cp.ParentID = Categories.CategoryID

Do you still get all the products and are any blank?

Author

Commented:
>its the same results as with the left join - you get all the products with that query<<
>The first query or the second.  This is important.
the first one, i didn't try the second one because i figured it would be more inclusive

with the query you sent me i got back very few results and they were all blank - i'm not sure how many because i'm running it on volusion, which is a shopping cart system, and they'll only let you see your query results as a csv file
Top Expert 2012
Commented:
>>the first one, i didn't try the second one because i figured it would be more inclusive<<
That was the whole point.  This is the situation:
You are using LEFT JOIN that means that if the data does not match then you will get a NULL for that value.  So you ask who cares?  Well, let me give you an example:
Supposing the Categories table does not match then that means that Categories.CategoryName is NULL, which means that the following is NULL:
p.ProductName + ' is made by ' + pe.ProductManufacturer + ' and is listed in our ' + Categories.CategoryName + ' and ' + cp.CategoryName

So here is my point:
Either use
1. LEFT JOIN and get all the results matched and the ISNULL function so that you default to some value when NULL as in ISNULL(Categories.CategoryName, 'No Category Name Available')
2. Use INNER JOIN and only rows for which you have all values.

Pick your posion.

Author

Commented:
i tried to run this query bellow... but got the error: String or Binary Data would be truncated.  How would you fix this?
UPDATE Products_Extended
SET METATAG_Title = p.ProductName+ ISNULL('is made by ' + pe.ProductManufacturer + ' and ' ,'') + ISNULL(' is listed in our ' + ISNULL(Categories.CategoryName + ' and ','') + ISNULL(cp.CategoryName,''), '')
FROM Products_Extended pe LEFT JOIN Products p ON pe.ProductID=p.ProductID LEFT JOIN (SELECT * FROM (
SELECT Categories_Products_Link.*, Categories.CategoryName, Categories.ParentID, row_number() over ( partition by Categories_Products_Link.ID ORDER BY Categories_Products_Link.CategoryID) rnum
                                FROM Categories_Products_Link,Categories
                                WHERE Categories.CategoryID=Categories_Products_Link.CategoryID ) temp
WHERE rnum = 1 ) as cp ON cp.ID=pe.ProductID LEFT JOIN Categories ON cp.ParentID=Categories.CategoryID

Open in new window

Top Expert 2012
Commented:
Simple.  You are trying to update a value that is longer then the column allows.  Execute this:

SELECT  LEN(p.ProductName +
                  ISNULL('is made by ' + pe.ProductManufacturer + ' and ', '') +
                  ISNULL(' is listed in our ' + ISNULL(Categories.CategoryName + ' and ', '') +
                  ISNULL(cp.CategoryName, ''), '')),
        p.ProductName +
                  ISNULL('is made by ' + pe.ProductManufacturer + ' and ', '') +
                  ISNULL(' is listed in our ' + ISNULL(Categories.CategoryName + ' and ', '') +
                  ISNULL(cp.CategoryName, ''), '')
FROM    Products_Extended pe
        LEFT JOIN Products p ON pe.ProductID = p.ProductID
        LEFT JOIN ( SELECT  *
                    FROM    ( SELECT    Categories_Products_Link.*,
                                        Categories.CategoryName,
                                        Categories.ParentID,
                                        row_number() OVER ( PARTITION BY Categories_Products_Link.ID ORDER BY Categories_Products_Link.CategoryID ) rnum
                              FROM      Categories_Products_Link,
                                        Categories
                              WHERE     Categories.CategoryID = Categories_Products_Link.CategoryID
                            ) temp
                    WHERE   rnum = 1
                  ) AS cp ON cp.ID = pe.ProductID
        LEFT JOIN Categories ON cp.ParentID = Categories.CategoryID
ORDER BY 1

This will show you the length of each row in descending order.  Then you have a choice:
A. Increase the length of METATAG_Title to allow for the longest value or
B. Decrease the length of the expression by using LEFT() or abbreviating or some other way.

Author

Commented:
thanks for all your help

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial