Jump to content

INSERT IGNORE DUPLICATE ROWS


Yohanne
Go to solution Solved by Yohanne,

Recommended Posts

Hi coders,

 

 

i have more than 100,000 rows of record and now i need it to insert by batch and to do so i need first to insert 10,000 lines/rows for as long as the system not getting log when it inserting. 

 

and now how could i avoid to make duplicate entry? and i am trying this code below but im not lack? 

$array[] = "('$user_name', '$audate_id', '$sid', '$bcode', '$Description', '$type', '$sold', '$audate')";
			
$sql ='INSERT IGNORE INTO pro_data(username, audate_id, stock_id, barcode, description, type, sold, audate_date) VALUES '.implode(',', $array);

Link to comment
Share on other sites

 


and now how could i avoid to make duplicate entry?

 

That depends on why you would get duplicates and what you want to do when you get duplicates.

 

The only way you can detect a duplicate is to create a rule in the database that enforces uniqueness, so the nest question is: what makes a record unique?

Iif you create a UNIQUE index on a combination of all columns that must have unique values then the database will refuse to insert those values twice in separate records.

The "DUPLICATE KEY" error that will generate can be intercepted with INSERT IGNORE, but be aware that IGNORE means "skip records that gave an error, *any* error" and you wil never get a message telling you which records were skipped. 

 

That may be what you want, it may not be what you want, you'll have to tell. :-)

Link to comment
Share on other sites

Hi barand and vinny,

 

 

in this scenario, i have two kinds of connection, ODBC and MySQL. the use of ODBC is to enable view records in every division and inserting it into MySQL using array. now in every each division there is UNIQUE records from "AUDIT_ID". and thats the only UNIQUE that i found. now since the division are using access there is a possible that there are same AUDIT_ID from other division. 

 

but for now i use IGNORE for inserting but it keeping inserting even the records is already there. and i think im doing the right syntax.  why and what wrong using IGNORE? is there any other way to avoid duplicate entry even in a one division?  and note i use array for insert since it contain hundred thousands of record in just one click. 

Link to comment
Share on other sites

 


now since the division are using access

 

Hold on, are you saying that you have several MS-Access applications running that all insert data into the same database? So two MS-Access applications generate the same audit_id for completely different data?

 

That would mean that there are no duplicate *records*, only duplicate audit_id's. You can't just ignore the duplicate audit_id's, because you would loose the rest of the data and the table would contain some records from the first division, som from the second, etc, depending on who created each audit_id first. The table would be completely useless.

 

You could simply change the audit_id depending on the division; division 1 would start at audit_id = 1000000, division2 at 2000000, etc. The size of the numbers would depend on how many records you expect to get before people decide that MS-Access it not the right choice :-)

 

A cleaner but more involved solution would be to combine the audit_id with a division_id, then the combination could be forced unique, and each division can use audit_id=1 as long as their division_id is different.

 

I guess we need more info about what exactly you are doing :-)

Link to comment
Share on other sites

Again,

 

for now i need advise if im doing correct.!  in this structure i have two connection odbc and mysql. the company has more than 100 branches store nationwide. now every branch are using ms access db and it is stand alone. now head office want to get easier access to get the transaction record from store/branch. 

 

now the first thing i do is to view the record using odbc connection and after viewing i save it into mysql. i insert it using array. now to avoid duplicate entry, i need to insert and update in single clicked.

 

and here is the scenario: insert into mysql and update into access. in access db i add field name temp_file since in beginning in view record i create a condition 

//in access 
"SELECT A, B, C  FROM ACCESSTABLE  WHERE temp_file = 'null'"; //to a sure that, that the record is not already in mysqldb. 
//in mysql
"INSERT INTO MYSQLDB(A,B,C)VALUES(P_A,P_B,P_C)";
//in access
"UPDATE ACCESSTABLE SET temp_file = 'TEMP_FILE' WHERE id = 'id'";
Link to comment
Share on other sites

please help with this, insert is working good while update in access in not.  i will appreciate any help

 

 

Warning: odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'audit_id'780215,'780216,'780217,'780218,'780219,'780220,'780221,'780222,'780223,'780224,'780225,'780226,'780227,'780228,'780229,'780230,'780232,'780233,'780231,'780234,'780235,'780236,'780237,'780238,'780239,'780240,'780241,'780242,'780246,'780245,'780244,'7'., SQL state 37000 in SQLExecDirect in C:\xampp\htdocs\protonSystem\store\view\save.php on line 37

foreach($_POST['audate_id'] as $row=>$name)
	{
		$user_name = mysql_real_escape_string($_POST['user_name'][$row]);
		$audit_id = mysql_real_escape_string($_POST['audate_id'][$row]);
		$stock_id = mysql_real_escape_string($_POST['sid'][$row]);
		$tran_type = mysql_real_escape_string($_POST['type'][$row]);
		$movement = mysql_real_escape_string($_POST['movement'][$row]);
		$audit_date = mysql_real_escape_string($_POST['a_date'][$row]);
				
				
$array[] = "('$user_name', '$audit_id', '$stock_id', '$tran_type', '$movement', '$audit_date')";
	}
	$sql ='INSERT INTO pro_data(username, audit_id, stock_id, transaction_type, movement, audit_date) VALUES '.implode(',', $array);

$result = mysql_query($sql)or die("$sql" .mysql_error());
			
if($result)
	{
		$conn=odbc_connect('db2','','');
	if (!$conn)
		{
		exit("Connection Failed: " . $conn);
		}
							
foreach($_POST['audate_id'] as $row=>$name)
	{
	$audate_id = mysql_real_escape_string($_POST['audate_id'][$row]);
										
		$AR[] = "'$audate_id";
										}
$odbc = "UPDATE Audit SET temp = '$_POST[temp_f]' WHERE audit_id".implode(',', $AR);
	rs = odbc_exec($conn,$odbc);
	}
Edited by JaysonDotPH
Link to comment
Share on other sites

im not already using ignore. just simple above. and my problem now is' to update and i can merge data  through to add id in mysql

$odbc = "UPDATE Audit SET temp = '$_POST[temp_f]' WHERE audit_id".implode(',', $AR);
Edited by JaysonDotPH
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.