Semi-complex query questions

[tomccabe]

I have a couple specific questions as I work to get more efficient/complex with my MySQL queries. In this specific project I have 3 related tables: products, categories and prod_cats. The last table functions essentially as a go between for category and position values (i.e. each product can be in multiple categories and has a specified position in each category).

 

- 1st question:

 

I have working PHP methods that add new categories and products. For instance the category one finds out how many rows are in the categories table in one query and assigns that value+1 to a variable, then adds that as the position, plus the category name to the table in another query. What I would like to do is roll this into one query. I tried things along the lines of:

 

INSERT INTO categories (category, position) VALUES ('$category', (SELECT MAX(position) FROM categories)+1)

 

This returned the error: "You can't specify target table 'categories' for update in FROM clause". It would be helpful to find any way to do this sort of operation.

 

- 2nd Question:

 

Well I answered this while posting this, but I thought I'd post where I found it in case it helps someone else. I was trying to find uncategorized products (no rows in the prod_cats table would have that product ID but the product does exist). This is for orphaned products after categories are deleted, etc or for products that the client doesn't want to bother categorizing while entering a batch. Obviously there's tons of other reasons to want to find something that is in one table but not another in relational databases. It uses NOT IN plus a subquery (it doesn't make a ton of sense to me why the subquery there does what it does, but hey, I'm learning).

 

http://ocaoimh.ie/mysql-finding-the-records-in-one-table-that-are-not-in-another-table/

[ignace]

INSERT INTO categories (category, position) SELECT '$category' AS category, MAX(position) + 1 AS position FROM categories

 

Although I assume category is a PK and therefor this should work as fine:

 

INSERT INTO categories (category, position) VALUES ('$category', '$curpos') ON DUPLICATE KEY UPDATE position = position + 1