MySQL Check and compare and if necessary create data.
- by user2979677
Hello I have three different tables
Talk
ID
type_id
YEAR
NUM
NUM_LETTER
DATE
series_id
TALK
speaker_id
SCRIPREF
DONE
DOUBLE_SID
DOUBLE_TYP
LOCAL_CODE
MISSING
RESTRICTED
WHY_RESTRI
SR_S_1
SR_E_1
BCV_1
SR_S_2
SR_E_2
BCV_2
SR_S_3
SR_E_3
BCV_3
SR_S_4
SR_E_4
BCV_4
QTY_IV
organisation_id
recommended
topic_id
thumbnail
mp3_file_size
duration
version
Product_component
id
product_id
talk_id
position
version
Product
id
created
product_type_id
last_modified
last_modified_by
num_sold
current_stock
min_stock
max_stock
comment
organisation_id
series_id
name
subscription_type_id
recipient_id
discount_start
discount_amount
discount_desc
discount_finish
discount_percent
voucher_amount
audio_points
talk_id
price_override
product_desc
instant_download_status_id
downloads
instant_downloads
promote_start
promote_finish
promote_desc
restricted
from_tape
discontinued
discontinued_reason
discontinued_date
external_url
version
I want to create a procedure that will check if select the id from talk and compare it with the id of product to see if there is a product id in the table and if there isn't then create it however my problem is that my tables talk and product can't talk, as id in talk is related to talk_id in product_component and id in product is related to product_id in product_component. Are there any ways for this to be done? I tried this,
CREATE DEFINER=`sthelensmedia`@`localhost` PROCEDURE `CreateSingleCDProducts`()
BEGIN
DECLARE t_id INT;
DECLARE t_restricted BOOLEAN;
DECLARE t_talk CHAR(255);
DECLARE t_series_id INT;
DECLARE p_id INT;
DECLARE done INT DEFAULT 0;
DECLARE cur1 CURSOR FOR SELECT id,restricted,talk,series_id FROM talk WHERE organisation_id=2;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
UPDATE product SET restricted=TRUE WHERE product_type_id=3;
OPEN cur1;
create_loop: LOOP
FETCH cur1 INTO t_id, t_restricted, t_talk, t_series_id;
IF done=1 THEN
LEAVE create_loop;
END IF;
INSERT INTO product (created,product_type_id,last_modified,organisation_id,series_id,name,restricted)
VALUES (NOW(),3,NOW(),2,t_series_id,t_talk,t_restricted);
SELECT LAST_INSERT_ID() INTO p_id;
INSERT INTO product_component (product_id,talk_id,position)
VALUES (p_id,t_id,0);
END LOOP create_loop;
CLOSE cur1;
END
Just wondering if anyone could help me.
