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

hillelbenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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

0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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

0
tigin44Commented:
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

0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

hillelbenAuthor Commented:
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
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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
0
hillelbenAuthor Commented:
the query runs but it sets METATAG_Title to blank.. any ideas why this is?
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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.
0
hillelbenAuthor Commented:
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
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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.
0
hillelbenAuthor Commented:
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
0
hillelbenAuthor Commented:
all 60,000 product have at least 1 category (with a Categories_Products_Link entry)
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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.
0
hillelbenAuthor Commented:
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

0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Oops... Missed ORDER BY Clause
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 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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hillelbenAuthor Commented:
thanks
i guess datetieme_col could be any field you in Categories_Products_Link
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> i guess datetieme_col could be any field you in Categories_Products_Link

Yes.. you are correct
0
hillelbenAuthor Commented:
oops i though this worked for everyone but on a lot of products the METATAG_Title was set to blank
0
hillelbenAuthor Commented:
any ideas why?
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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.
0
hillelbenAuthor Commented:
same results with the inner joins
0
hillelbenAuthor Commented:
sorry i didn't try the query i tried changing the lefts to inners in the update... i'll try this now
0
hillelbenAuthor Commented:
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)
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> 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.
0
hillelbenAuthor Commented:
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
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> whats SSMS?

I referred SQL Server Management Studio. It comes as part of your SQL Server Client Installation or Server installation.
0
hillelbenAuthor Commented:
don't have that to use. they just let me run queries from their web interface
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.