MySQL Forums
Forum List  »  General

Re: No duplicate records, ignore
Posted by: Nunya Beeznass
Date: November 19, 2020 09:10PM

Ya know, I didn't ask for help with INSERT IGNORE or ON DUPLICATE KEY specifically. I asked for help avoiding duplicates. I already came up with a solution using python to execute what I wanted successfully. Pretty sure, however, this is more effecient:

python calls stored procedure with list and other variables. Stored procedure creates temp table, then inserts with join.

new.py:

# create stored procedure code
sql_string = "call spnew('%s','%s',\"%s\",%s,'%s','%s');" % (
exch,
call,
', '.join(columns).lower(),
values_str.lower(),
curs,
curt
)

spnew.sql:

USE mydb;
DROP PROCEDURE IF EXISTS spnew;
DELIMITER //
CREATE PROCEDURE spnew (exch VARCHAR(10),calltype VARCHAR(50),pkeys TEXT,prows TEXT,base VARCHAR(10),pair VARCHAR(10))
BEGIN
DECLARE strLen INT DEFAULT 0;
DECLARE SubStrLen INT DEFAULT 0;
DECLARE tableName VARCHAR(50) DEFAULT CONCAT('ex',calltype);
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
END;
START TRANSACTION;
-- create temporary table
CREATE TEMPORARY TABLE exnewtemp LIKE tableName;
-- begin loop for insert
insert_data: LOOP
SET strLen = CHAR_LENGTH(prows);
-- insert row into temp table
INSERT INTO exnewtemp (pkeys,exid) VALUES (SUBSTRING_INDEX(prows, ',', 1),(SELECT et.exid FROM exs et WHERE et.exabbr = exch),(SELECT curid FROM curs WHERE abbr = base),(SELECT curid FROM curs WHERE abbr = pair));
SET SubStrLen = CHAR_LENGTH(SUBSTRING_INDEX(prows, ',', 1))+2;
-- reduce list by used item
SET prows = MID(prows, SubStrLen, strLen);
IF prows = '' THEN
LEAVE insert_data;
END IF;
END LOOP insert_data;
-- insert new, unique records in perm table
INSERT INTO tableName
SELECT n.* FROM exnewtemp n
LEFT JOIN tableName e on (n.exid = e.exid AND n.at = e.at AND n.bid = e.bid and n.p = e.p and n.pid = e.pid)
WHERE e.id IS NULL;
COMMIT;
END //
DELIMITER ;

Options: ReplyQuote


Subject
Written By
Posted
November 17, 2020 09:36AM
Re: No duplicate records, ignore
November 19, 2020 09:10PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.