I have two tables:
products_discounts. If a product is designated as a "Top 10" product, and it is missing a defined discount in
products_discounts, I want to insert a record into
products_discounts for each missing record. The inserted values would consist of the
product_id and two other hard-coded values in the INSERT INTO.
id, product_id, name, top_ten
id, product_id, discount_amount, discount_description
The following is how I plan to do this manually, but since I'll be repeating this process a few times a year, I'm looking to see if this can be done in a few lines of SQL.
(1) Get the list of products that meet my criteria.
SELECT p.product_id, pd.discount_amount FROM products AS p LEFT OUTER JOIN products_discounts AS pd ON p.product_id = pd.product_Id WHERE p.top_ten = 1 AND pd.product_id IS NULL
(2) Use a text editor to construct each individual INSERT INTO statement. I'd use the output of step 1 to get the
product_ids but the other two values would remain the same (hard-coded).
INSERT INTO products_discounts (product_id, discount_amount, discount_description) VALUES ('<product_id goes here>', 10, 'Top 10 Product')
Can these steps be combined into a few lines of SQL?