Solved

update query

Posted on 2009-07-15
26
318 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
ID: 24857401
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
ID: 24857414
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
ID: 24857417
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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

Author Comment

by:hillelben
ID: 24857632
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
ID: 24857640
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
ID: 24857658
the query runs but it sets METATAG_Title to blank.. any ideas why this is?
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24857678
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
ID: 24857703
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
ID: 24857719
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
ID: 24857738
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
ID: 24857881
all 60,000 product have at least 1 category (with a Categories_Products_Link entry)
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24857992
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
ID: 24858145
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
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 500 total points
ID: 24858527
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
ID: 31603626
thanks
i guess datetieme_col could be any field you in Categories_Products_Link
0
 
LVL 57

Expert Comment

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

Yes.. you are correct
0
 

Author Comment

by:hillelben
ID: 24858875
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
ID: 24858881
any ideas why?
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24858969
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
ID: 24859033
same results with the inner joins
0
 

Author Comment

by:hillelben
ID: 24859064
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
ID: 24859174
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
ID: 24859581
>> 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
ID: 24859926
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
ID: 24859967
>> 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
ID: 24860113
don't have that to use. they just let me run queries from their web interface
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
average of calculation (TSQL) 4 26
AJAX pass along a variable 3 46
table joins in qry 17 65
IIF in access query 19 27
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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…

830 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