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:
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.
//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)