Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Using condition logic with MySQL SQL statement syntax?

Posted on 2008-10-13
11
Medium Priority
?
806 Views
Last Modified: 2013-12-13
Evening!

I have a question regarding the usage of condition logic in SQL statements.

Here is the scenario.

I have a table, mmproductcategory that records are inserted into/deleted from in order to control what items exist in what category of our product catalog.

table structure is:

MMPRODUCTCATEGORY
-------------------------------
PKMMCategoryID (bigint, 20)
FKCategoryID (bigint, 20)
FKProductID (bigint, 20)

Our code reads the highest primary key entry in the mmproductcategory table in order to determine the display order of products in the catalog. I.E., the newest records/items added to any category are always displayed first.

I now need to do a blanket update of all products $9.99 and less and move them into the sales category.

That part's not a problem, the query for that is fairly simple. (see attached to get a better idea of what I'm working with) However, here's the issue.

If I have products in 2 categories, such as portable_electronics, AND the sale category, and I do a blanket update, it's going to insert a second record for sale, and thus existing products in the sales category will be pushed to the front of the display order, even though they're not new items.

My question: is there a way to, either with PHP or MySQL, check if a record already exists before performing the insert? For example, Product A has an FKProductID of 55, and the FKCategoryID of Sale is 64. This item exists in portable_electronics but also already exists in sale.  Can I, before performing the price-based move to the Sale category, somehow check if that FKProductID AND FKCategoryID already exists as a record in mmproductcategory?

I'm guessing there's a probably a way via script or stored procedure, but I've not worked with those types of functions much yet.

Thanks!


//Query to insert all products into the sales category where product //value = $9.99 or less 
 
insert into mmproductcategory(Fkproductid,fkcategoryid) (select distinct(mmproductgroupdetail.FKProductid),<CATEGORY ID OF SALE> from mmproductgroupdetail,MMPRODUCTCATEGORY where mmproductgroupdetail.price <=9.99)

Open in new window

0
Comment
Question by:jmoriarty
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22703353
If you could afford to check the Primary Key of the table, then, yes.  The Insert ... On Duplicate Key Update ... syntax is very cool.

In this case, however, I think you need to do an Update using an INNER join (to update existing) followed by an INSERT using an OUTER join (to insert those that don't already exist).

Want code for this kind of logic?
0
 

Author Comment

by:jmoriarty
ID: 22703677
Yeah, I was trying to think of a way to check the PK as well, but there's no reference point for it outside mmproductcategory.. For example, you could have 2 entries for the same item under the sales category, and it'd show up twice in the admin panel, etc.

I think I understand what you're talking about with the second paragraph, I hadn't considered using an inner/outer join setup. If you have the time, I'd gladly take you up on the offer for example code.

Thanks!
-James
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22703941
Here are the statements if I didn't make a mistake ...

Honestly, the first one won't do anything though.  Maybe I'm misunderstanding ... b/c I'm updating it to the ID of the SALES category ... where it's already the sales category.

I think you actually need only the INSERT statement.

Update mmproductgroupdetail INNER Join MMPRODUCTCATEGORY 
	on MMProductGroupDetail.FKCategoryID = <CATEGORY ID OF SALE> AND
	MMProductGroupDetail.FKProductID = mmproductgroupdetail.FKProductid
SET MMProductGroupDetail.FKCategoryID = <CATEGORY ID OF SALE>
where mmproductgroupdetail.price <=9.99
 
insert into mmproductcategory(Fkproductid,fkcategoryid) 
(select distinct(mmproductgroupdetail.FKProductid),<CATEGORY ID OF SALE> 
from mmproductgroupdetail Left Join MMPRODUCTCATEGORY 
	on MMProductGroupDetail.FKCategoryID = <CATEGORY ID OF SALE> AND
	MMProductGroupDetail.FKProductID = mmproductgroupdetail.FKProductid
where mmproductgroupdetail.price <=9.99
	AND MMProductGroupDetail.PKMMCategoryID IS NULL)

Open in new window

0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 

Author Comment

by:jmoriarty
ID: 22704184
Hrm, I see what you're getting at, but the structure is a bit more limiting than that I'm afraid. The only common column mmproductgroupdetail shares with mmproductcategory is fkproductid. :/  So we'd need a way to check against the fkproductid + category id of sale (which is 64 in this case) combination.

Something like:
IF($fkproductid, 64) EXISTS
DO
NOTHING
ELSE
insert into mmproductcategory(.....)

Obviously not nearly that simple, that's my thinking process on the logic flow.

You are right about the first one though, no need for an update, just the insert, now that I look at it.

It's an interesting problem because there's no secondary reference to check against the categoryid that I can see. I'm far from an expert on the subject just yet though.
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22704280
Try my INSERT statement then ... the LEFT JOIN ... Where PK... is NULL does the same thing as an If Not Exists -- but in set-based logic.
0
 

Author Comment

by:jmoriarty
ID: 22705116
My apologies if I'm missing something -- what would I join on, since mmproductgroupdetail doesn't have fkcategoryid?
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22705401
Oh ... I guess the join needs to get a bit more complex.  What is the relation between mmproductgroupdetail and mmproductcategory ?

Is there another table involved there?
0
 

Author Comment

by:jmoriarty
ID: 22705971
The relation is between the FKProductID. Basically the product ID is what links everything. mmproductgroupdetail relates to the primary key of product table, the fkproductid of mmproductcategory table, and several other smaller tables, like a bin_location table etc through the fkproductid tag.

The flow for categories is ->

Category table:
PKCategoryID
CategoryName
FKCategoryID (relates to itself for sub categories)

Product:
PKProductID
assorted attributes


MMProductGroupDetail:
FKProductID relating to product.pkproductid
SKU
Price
Quantity
and related attributes

And mmproductcategory has:
PKMMCategoryID
FKCategoryID
FKProductID

Essentially, since I already know the categoryid of sale, the join will be between the mmproductgroupdetail and mmproductcategory I think, the difficult part (at least, for me) is finding which records exist since there's no reference to category in mmproductgroupdetail as well.
0
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 2000 total points
ID: 22710915
OK, but we're putting all products  w/ a price < 9.99 in the "sale" category ...

How's this one?


insert into mmproductcategory(Fkproductid,fkcategoryid) 
(select distinct(Product.FKProductid),<CATEGORY ID OF SALE> 
from Product Left Join MMPRODUCTCATEGORY 
        on mmproductcategory.FKCategoryID = <CATEGORY ID OF SALE> AND
        Product.PKProductID = mmproductcategory.FKProductid
where Product.price <=9.99
        AND mmproductcategory.PKMMCategoryID IS NULL);

Open in new window

0
 

Author Comment

by:jmoriarty
ID: 22825940
Hi Daniel,

Sorry for the late reply, I got side tracked on another project. Yes, that did the trick, and makes sense.

Thank you!

0
 

Author Closing Comment

by:jmoriarty
ID: 31407081
Thanks again!
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.
Suggested Courses

604 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