asked on

update query

i need a query to set Products_Extended.METATAG_Title to be like this....
[Product Name] is made by [ProductManufacturer] and is listed in our [Category] and [Subcategory]

where ProductName is in the Products table and ProductManufacturer and METATAG_Title are in the Products_Extended table there is a ProductID field in each table that has equivalent values when the product is the same. and the is a table Categories_Products_Link. that has fields ID (which coresponds to the ProductID) and CategoryID which has an entry for each category that a product belongs to (a product may have more than one category). The Categories table has CategoryName and ParentID of the parent Category. See the query bellow to get an idea - i think there's probably some syntax mistakes

I'm running this query on Volusion which is a shopping cart system so i can't see the sql errors. I ran the query bellow which has some error because it didn't work. For now its ok even though a product may have many categories we can just take any one an use it

thanks

UPDATE Products_Extended LEFT JOIN Products p ON Products_Extended.ProductID=p.ProductID LEFT JOIN (SELECT Categories_Products_Link.*, Categories.CategoryName FROM (Categories_Products_Link,Categories) WHERE Categories.CategoryID=Categories_Products_Link.CategoryID) AS cp ON cp.ID=Products_Extended.ProductID LEFT JOIN Categories ON cp.ParentID=Categories.CategoryID SET Products_Extended.METATAG_Title = p.ProductName+' is made by ' + Products_Extended.ProductManufacturer + ' and is listed in our ' +Categories.CategoryName + ' and ' + cp.CategoryName

Open in new window

Raja Jegan R

Syntax corrected query:

Kindly revert if any additional logic change required.

UPDATE Products_Extended
SET pe.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 Categories_Products_Link.*, Categories.CategoryName FROM (Categories_Products_Link,Categories) WHERE Categories.CategoryID=Categories_Products_Link.CategoryID) AS cp ON cp.ID=pe.ProductID LEFT JOIN Categories ON cp.ParentID=Categories.CategoryID

Open in new window

Raja Jegan R

One more mistake:

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 Categories_Products_Link.*, Categories.CategoryName FROM Categories_Products_Link,Categories WHERE Categories.CategoryID=Categories_Products_Link.CategoryID) AS cp ON cp.ID=pe.ProductID LEFT JOIN Categories ON cp.ParentID=Categories.CategoryID

Open in new window

tigin44

use this

UPDATE E
SET Products_Extended.METATAG_Title = p.ProductName+' is made by ' + Products_Extended.ProductManufacturer + ' and is listed in our ' +Categories.CategoryName + ' and ' + cp.CategoryName
FROM Products_Extended E
	LEFT JOIN Products p ON E.ProductID=p.ProductID 
	LEFT JOIN (SELECT Categories_Products_Link.*, Categories.CategoryName 
				FROM (Categories_Products_Link,Categories) 
				WHERE Categories.CategoryID=Categories_Products_Link.CategoryID) AS cp ON cp.ID=Products_Extended.ProductID 
	LEFT JOIN Categories ON cp.ParentID=Categories.CategoryID

Open in new window

hillelben

ASKER

i forgot to mention that ParentID is in the Categories table so we have to add that in the SELECT

JOIN (SELECT Categories_Products_Link.*, Categories.CategoryName, Categories.ParentID
FROM (Categories_Products_Link,Categories)
WHERE Categories.CategoryID=Categories_Products_Link.CategoryID) AS cp

i tried rrjegan17s query just now, with the addition above... i'm not sure why but the METATAG_Title fields are all empty

with tigin44s query there was a sytax error, but i'll have to talk to tech support to find out what it was

Raja Jegan R

Small change to the module you just added now to correct syntax mistakes:

JOIN (SELECT Categories_Products_Link.*, Categories.CategoryName, Categories.ParentID
FROM Categories_Products_Link,Categories
WHERE Categories.CategoryID=Categories_Products_Link.CategoryID) AS cp

Hope this helps

hillelben

ASKER

the query runs but it sets METATAG_Title to blank.. any ideas why this is?

Raja Jegan R

Its because you do a LEFT OUTER join on this result set.
If you dont have any ProductID from this result set, then it would be NULL in the final SELECT statement.

Hope this is what you refer to.

hillelben

ASKER

actually i just did a query SELECT METATAG_Title, ProductManufacturer FROM Products_Extended WHERE METATAG_Title<>''
to find if it worked for anything it did work for 7 products, but for the other 60,000 its all black i guess

Raja Jegan R

You have to issue IS NOT NULL instead of <> '':

SELECT METATAG_Title, ProductManufacturer FROM Products_Extended WHERE METATAG_Title IS NOT NULL

to find all records.

hillelben

ASKER

with this part
JOIN (SELECT Categories_Products_Link.*, Categories.CategoryName, Categories.ParentID
FROM Categories_Products_Link,Categories
WHERE Categories.CategoryID=Categories_Products_Link.CategoryID) AS cp

there are lots of Categories_Products_Link entries for each ID (Product ID) really we just want one (anyone of them - is this causing the problem?) can you do this with UNIQUE for ID... i'm not sure about the syntax of this

hillelben

ASKER

all 60,000 product have at least 1 category (with a Categories_Products_Link entry)

Raja Jegan R

Then kindly replace 1 record for each and every product id.

JOIN (SELECT * FROM (
SELECT Categories_Products_Link.*, Categories.CategoryName, Categories.ParentID, row_number() over ( partition by Categories_Products_Link.Productid) rnum
FROM Categories_Products_Link,Categories
WHERE Categories.CategoryID=Categories_Products_Link.CategoryID ) temp
WHERE rnum = 1 ) as cp

Kindly revert if this is not what you mentioned.

hillelben

ASKER

got an error
The Ranking Funcion "row_number" MUST HAVE an ORDER BY clause.

i'm sending the code so you can make sure i didn't make a mistake

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) 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

ASKER CERTIFIED SOLUTION

Raja Jegan R

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

hillelben

ASKER

thanks
i guess datetieme_col could be any field you in Categories_Products_Link

Raja Jegan R

>> i guess datetieme_col could be any field you in Categories_Products_Link

Yes.. you are correct

hillelben

ASKER

oops i though this worked for everyone but on a lot of products the METATAG_Title was set to blank

hillelben

ASKER

any ideas why?

Raja Jegan R

First replace all LEFT OUTER joins with INNER JOIN to find out how many records you obtain in the SELECT statement.

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 datetieme_col) 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

Then replace INNER to LEFT JOIN one by one to find out where exactly the blanks come.
That should identify you to find out.

hillelben

ASKER

same results with the inner joins

hillelben

ASKER

sorry i didn't try the query i tried changing the lefts to inners in the update... i'll try this now

hillelben

ASKER

the query fails - but i'm not sure if its because of syntax error or another reason - i'm running it on the volusion servers and it just doesn't come back with an error code (which is a fault in their software)

Raja Jegan R

>> but i'm not sure if its because of syntax error or another reason

You can click on Parse button right to Execute button in SSMS to check the syntax. If you need any help in that kindly give the query which you tried out.

>> it just doesn't come back with an error code (which is a fault in their software)

Kindly revert if any help required from me.

hillelben

ASKER

whats SSMS? i don't think i can use it with volusion they just have a web page where you can submit the query you want to run

Raja Jegan R

>> whats SSMS?

I referred SQL Server Management Studio. It comes as part of your SQL Server Client Installation or Server installation.

hillelben

ASKER

don't have that to use. they just let me run queries from their web interface