Solved

update query

Posted on 2009-07-15
26
312 Views
Last Modified: 2012-05-07
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

0
Comment
Question by:hillelben
  • 14
  • 11
26 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
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
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
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
 
LVL 26

Expert Comment

by:tigin44
Comment Utility
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
 

Author Comment

by:hillelben
Comment Utility
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
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
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
 

Author Comment

by:hillelben
Comment Utility
the query runs but it sets METATAG_Title to blank.. any ideas why this is?
0
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
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
 

Author Comment

by:hillelben
Comment Utility
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
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
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
 

Author Comment

by:hillelben
Comment Utility
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
 

Author Comment

by:hillelben
Comment Utility
all 60,000 product have at least 1 category (with a Categories_Products_Link entry)
0
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
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
 

Author Comment

by:hillelben
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:hillelben
Comment Utility
thanks
i guess datetieme_col could be any field you in Categories_Products_Link
0
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
>> i guess datetieme_col could be any field you in Categories_Products_Link

Yes.. you are correct
0
 

Author Comment

by:hillelben
Comment Utility
oops i though this worked for everyone but on a lot of products the METATAG_Title was set to blank
0
 

Author Comment

by:hillelben
Comment Utility
any ideas why?
0
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
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
 

Author Comment

by:hillelben
Comment Utility
same results with the inner joins
0
 

Author Comment

by:hillelben
Comment Utility
sorry i didn't try the query i tried changing the lefts to inners in the update... i'll try this now
0
 

Author Comment

by:hillelben
Comment Utility
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
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
>> 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
 

Author Comment

by:hillelben
Comment Utility
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
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
>> whats SSMS?

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

Author Comment

by:hillelben
Comment Utility
don't have that to use. they just let me run queries from their web interface
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now