INSERT IGNORE DUPLICATE ROWS

[Yohanne]

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);

[Barand]

The values need to be inside ()s

INSERT IGNORE INTO table (a, b) VALUES (1,2)

Edit: Ignore

Edited September 19, 2013 by Barand

[vinny42]

 

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. :-)

[Yohanne]

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. 

[vinny42]

 

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 :-)

[Yohanne]

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'";

[Yohanne]

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 September 23, 2013 by JaysonDotPH

[vinny42]

 

for now i need advise if im doing correct.!  

 

What's the point? You are just going to ignore what's being said and do your own thing, just like you are doing now. I've already told you that you cannot simply merge data, but you do it anyway.

[Yohanne]

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 September 23, 2013 by JaysonDotPH

[vinny42]

 

m not already using ignore.

 

I know that, but you are ignorING what is being said. Like now, you are asking about how to update, but do you know why you want to update, or even that you *can* update these records?

[Yohanne]

yes, i know what im doing and i know why i need to update the following field. i im just asking now for the right syntax code for multiple update.  

[vinny42]

 

and i know why i need to update the following field

 

You cannot update the field, that's the problem. You have to store *all* the data and you cannot, ever, change any of it, in any way.

So updating is not even relevant i the first place.