how to handle duplicate keys in insert

Hi people

I have the following table definition:
Code: CREATE TABLE `suggested_synonyms` ( `Last_Name` varchar(255) NOT NULL, `Synonym` varchar(255) NOT NULL, PRIMARY KEY (`Last_Name`,`Synonym`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; The table stores mapping from a last name to a synonym.
I need to insert multiple rows into the table. Something like:
Code: insert into suggested_synonyms (`Last_Name`,`Synonym`) values ('smith', 'smeeth'), ('smith', 'smeath'); the problem is that some of the mappings may already exist in the table.
I tried to use the ON DUPLICATE KEY UPDATE syntax to prevent duplicate entries, but apparently I am not doing it correctly:
Code: insert into suggested_synonyms (`Last_Name`,`Synonym`) values ('smith', 'smeeth'), ('smith', 'smeath') ON DUPLICATE KEY UPDATE; As I need to specify the columns to update.

Is there a way to insert multiple rows in a single query and still be safe from duplicate rows

Posted On: Thursday 25th of October 2012 11:13:27 PM Total Views:  422
View Complete with Replies

Related Messages:

Social website "friends lists". How is the data handled? Table design...   (210 Views)
How would you think that busy sites like facebook and myspace manage each users' friends list. With the sites having millions if not hundreds of millions of users, and some users have thousands of one giant table with all friend connections seems like a bad idea. Each user get it's own table Then you could potentially end up with millions of tables. That doesn't seem like good design either. Granted, a table would only really need to be a few columns. record id (bigINT), member_id(int), friend_id(int), If you had 100million users and on average 100 friends per user, that's 10billion records. But it'd be 10billion small, tiny records with only integer based columns. While that'd be a huge record set, you could probably do a lot with caching, and creating different views. Or, just do a table for each letter of the alphabet. If your last name begins with T, you go int the T table for friends. Then that table only contains the friend records of members who's last name begins with T. That would only require 26 tables, and could then cut 10billion records into tables with only 390million records. DBA's, how would you approach this
How to handle error in MySQL script   (212 Views)
How can I handle error in mySQL script in c# we use try.. catch.. so, what about MySQL script
How many connection a mysql can handle and how can I tell it's overloaded?   (197 Views)
Hi. I wonder how many connections a mysql server can normally handle And how can I tell (tools/technique) tell will tell me it's about to get overloaded that I need to do something
Which mysql version is more able to handle heavy traffic   (179 Views)
hello, we are currently running mysql 4.1. we have problems with a php/mysql website that generates a lot of traffic and relies heavily on mysql (it's a "find your old schoolmates" community website). Sometimes it just says "too many connections", or just takes a long while before rendering the page. Would upgrading to mysql 5 help What are the best mysql settings in order to optimize the performance
How to handle this Query   (181 Views)
Hi I have a query I don't know how to handle I have a leaders2subordinates table: Code: delimiter $$ CREATE TABLE `leaders2subordinates` ( `leader_uid_number` int(11) NOT NULL COMMENT 'Leaders user number', `leader_collection_id` smallint(4) NOT NULL COMMENT 'leaders type designation', `sub_uid_number` int(11) NOT NULL COMMENT 'Subordinate uid_number', `sub_collection_id` smallint(4) NOT NULL COMMENT 'Subordinate user type', PRIMARY KEY (`leader_uid_number`,`leader_collection_id`,`sub_uid_number`,`sub_collection_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1$$ That has this data leader_uid_number leader_collection_id sub_uid_number sub_collection_id 10006 9 10004 11 10007 10 10005 11 10008 8 10006 9 10009 8 10007 10 I have a leads table where one of its' fields is counselor_id; say for this example it is 10004I have a users table where all users designated by 10004 - 10009 are located. This users table includes u.first_name and u.last_name fields I can get a leads counselor by the following: Code: SELECT CONCAT(cnsl.first_name, ' ', cnsl.last_name) as 'Counselor' FROM leads as l INNER JOIN users as cnsl ON l.counselor_id = cnsl.uid_number WHERE l.lead_id = 1; This returns 'Bill Parker' as the Counselor. But things get more complicated from here as I also want to find the that same leads Sales Manager I have to determine who 'Bill Parkers' Sales Manager. To do this I need to involve the leaders2subordinates table. If 'Bill Parker' has an uid_number of 10004 and I want to return his Sales Manager then do I have enough information to return the Leads' counselor and the sales manager related to that leads counselor If so what would be the best way to return a row like: Lead Counselor Sales Manager Paulette Johnson Bill Parker Anita Ward
how to handle different datas for different users in a registration table   (246 Views)
all I am designing a table.My situations are as follows I have so many user types.I am defining the user types as type1,type2,type3 etc I have a registration system and I have different data for each types.Even admin can add the data for these types I am not getting an idea how to handle my registration table.Should I store everything in a single table for all types please help me
Remotely hosted service - how to handle users/clients?   (213 Views)
I'm in the process of planning a website that would provide a service. Each client (company) would be presented with their own space, which would be private to them. I'll use an example, a Project Management service. A user (as in, a person working for one of our clients) logs in to manage one of their projects. They are presented with only their data. Each client's data is contained in tables with their custom prefix. So client A's table have the prefix of 'c1_', client B has 'c2_' and so on. The reason for keeping the data separate is so that it can easily be exported, deleted, etc. There would need to be a table to link the clients to their table prefixes. That's fine. But where do I store the users If is logging in with an email/password combination does that mean I store all of the users together Because it wouldn't be feasible to query every prefixed table for a particular user. e.g. c1_users, c2_users, etc. -- just to find out which client they're from (which then leads us to which table prefix to use).
How to handle references to multiple tables which exclude one another?   (230 Views)
I am designing a product catalog of sorts. For now there will be three product types, and it is unlikely that there will significantly more different types of products. Because these products share very few properties, I thought it would be best to use a different table for each product. I'm too lazy to type the actual details of these products (sorry), but one can think of them as different sized boxes whose types are named A, B and C, and A is the biggest and C the smallest So let's talk about boxes. These boxes have specific relations: 1. only a box of type A can be by itself, other boxes must be inside a bigger box 2. ergo, a B must always be inside an A 3. a C can be inside an A or a B 4. but an A can immediately contain only either a B or a C, so if it contains both, the C must be inside a B 4. a single A can contain multiple Bs, and a single B can contain multiple Cs 5. but there can be only one C immediately inside an A So at the moment I have these tables (database is MySQL): Code: Product_A - id - properties... Product_B - id - product_a_id - properties... Product_C - id - product_a_id - product_b_id - properties... This "works". It's simple and easy to handle. I can maintain integrity outside database. I can make sure that Product_C.product_a_id is NULL (or zero) if Product_C.product_b_id is not, and vice versa. But I don't really like it. It seems clumsy, amateurish and I would really like the database to take care of data integrity. I thought about creating just one Products table and marking relationships with 'parent_id' and separating the properties into either several tables (one for each product) or just one big table (with references to Property_types table, or something like that), but it seems like overkill and would unnecessarily complicate the queries. (Or at least the way I thought I'd do it would.) There would also still be problems with data integrity. I could easily have an A contain Bs and Cs side to side. in all, to me it seems like even worse a solution. So I'm asking for some pointers here. I think I'm happy with having the three tables, but not the way Product_C table stands at the moment. I just don't have any idea how to make it better. To me it seems like I'd need to have one foreign key pointing to multiple tables (this is a recurring problem in this database, actually), and that smells like a design error.
how best to handle yes/no columns?   (250 Views)
I'm a total begginer, and I'm programming a database for my stock home plans. I have them in a spreadsheet and am importing them into a MySQL database table. I have 6 or 7 criteria that are Yes/No questions for each house. (It either has a basement or it doesn't. It either requires steel in the framing or it doesn't - and so forth). Each house could fit none, one or more of these criteria. I could have additional criteria in the future. Right now I have a separate column in the Excel spreadsheet with Yes or No. What's the smartest way to handle this in a database Should I still have a column for each, with a 0 or a 1 Or - is there a better way
Best way to handle different user types in DB?   (157 Views)
, I am having trouble with the following: I currently have a DB setup that has all the users in one table. However, there are subtle differences between these user types such that for some users, certain fields in the table need not be specified. Now sure, I can set those fields to NULL, but the fields I am talking about are Foreign Keys. My solution up until this point has been to set each field as NOT NULL, and allow the user to choose "n/a" as a value for those fields. This of course requires that my referencing tables have "n/a" as values as well, and I just don't like that. I can set up three separate user tables, which would eliminate the need for redundant fields, however this makes the login process more difficult. Right now, the user table has the login information. If I have three different tables, all with their own login information (username, password), then when the system validates, it would have to query three different tables and see which one yields a match. Again, this is not good. So, I say to myself, why not have all of the login information in one table I think this is a good idea, but I'm not sure how I should set up the references to the tables with the other user information. What do you think What is normally done in this type of situation
Best way to handle collations for multilingual column?   (272 Views)
Hi , just a quick question, how do you handle different sort ordering in your multilingual web apps Assuming DB design like this , having rows for multiple languages in one column, how do you retrieve results in correct order for particular language It is neccessary to call COLLATE() for every single ORDER BY clause or is there any better way to achieve this
Duplicate records - how to not show duplicates?   (193 Views)
I have a MYSQL Table which has many e-mail address duplicates. When our programmer did a newsletter function, he didn't add anything to check to see if they were already added. So, ultimately, what we have now, is a newsletter database of peoples e-mail addresses, of which there are many people in there more than once. What is a MYSQL string that we can run that will basically take all of the e-mail addresses in this table, and give us the output of just one (it won't show the duplicates). I would rather keep the duplicates where they are (they show another date that they joined the newsletter), but for our mailing list, I don't want the duplicate e-mail addresses in our mailing list. Any suggestions
Impossible duplicate...   (185 Views)
, i have a very strange case; when i submit the following query Code: select id from text_tmp limit 25; my result is : Code: +----+ | id | +----+ | 1 | | 2 | | 4 | | 5 | | 6 | | 7 | | 8 | | 10 | | 11 | | 13 | | 15 | | 16 | | 17 | | 18 | | 20 | | 21 | | 23 | | 24 | | 25 | | 26 | | 27 | | 28 | | 30 | | 31 | | 32 | +----+ therefore, id 22 (and a few more) is missing... and when i ask for : Code: UPDATE text_tmp SET id='21', place=((text_tmp.original_id % 5) +1), round=round-1, occupied='no' WHERE occupied='yes' ORDER BY id DESC LIMIT 1; it returns me Code: DBD::mysql::db do failed: Duplicate entry '22' for key 1 .... how come p.s.: meanwhile i'm googling for help on the issue but the last 2 hours haven't brought be any info yet in my needs.
Finding duplicates that also do not match ! Almost duplicates   (233 Views)
Guys. I have been using MySQL for a couple of years but am truly stuck with this one and would appreciate anyone's help. I am searching a large database of almost 1 million records so the statement needs to be as short as possible. In 1 table I am searching through 4 columns 3 of them need to match (duplicates) and one of them must be different. Vendor Name Invoice No Invoice Date Invoice Amount Boots The Chemist 001 01/01/07 10,000 Boots 001 01/01/07 10,000 Boots The Chemist 001 01/01/07 10,000 Boots The Chemist 001 01/01/07 10,000 So I need to do a search that would point out the above selection because the last 3 columns match and the Vendor Names do not match. But if all four columns match (duplicates) I need to ignore. Vendor Name Invoice No Invoice Date Invoice Amount WH Smiths 001 01/01/07 5,000 WH Smiths 001 01/01/07 5,000 WH Smiths 001 01/01/07 5,000 So the key is to display where the last 3 columns match and the first column does not match. So far, I have this bit it could be completely wrong: mysql = "SELECT * FROM (SELECT *, COUNT(*) AS Duplicates FROM tblMain GROUP BY InvAmo, InvNum, InvDate HAVING COUNT(*) > 1) a1 " &_ "GROUP BY VendName HAVING COUNT(VendName) < Duplicates" The nested select statement finds all duplicates for the last 3 fields and the outer select statement should then group by the Vendor Name and see if there are any difference but obviously it is not working... Many
Make fieldB unique if fieldA is duplicate   (157 Views)
I'm sure that there is a way to do this thru SQL, but my inexperienced toying is not producing results. The way my client wants the record id's to look is 200609220643001 (200609220643 = year, month, date, hour, minute... generated in the PHP submit and stored in fieldA) + (001 = unique id generated if fieldA if duplicate). Currently fieldA is a varChar and fieldB is a tinyInt with unsigned_zerofill/auto_increment... but I don't really want fieldB to auto_increment unless fieldA is a duplicate. I've checked out this link with no luck: example-auto-increment. Whenever I used the settings described I would get errors. I'm using MySQL v.4.1.21, and phpmyadmin v. Any ideas on the appropriate table setup to do this, or will I have to write a PHP function to check for duplicates
form handler not working, have a look   (219 Views)
I'm trying to use a form to add some info to my DB. I'm a noob so I have cut and pasted my way to get to this point, but I am stuck as to why this is not working. There are 2 parts to my code... the first is the actual form and then the second is the form function. This is the form: PHP Code: OpenTable(); //thisisadd-officeform { print""; echo"Usetheformbelowtoaddadditionaloffices." ."" ."Step1,pleasecheckwherethisofficeislocated." .""._FW."" .""._NW."" .""._N."" .""._S."" .""._E."" .""._CH."" .""._ML."" .""._RF.""; echo"Step2,pleasechangeinfobelowtoalteroraddanadditionalofficelocationwhereyoucanbefoundbythepublic." .""._PRONAME.":".$userinfo['name']."" .""._PRACTNAME.":" .""._ADDRESS1.":" .""._ADDRESS2.":" .""._CITY.":" .""._STATE.":" .""._ZIP.":" .""._PHONE.":" .""._FAX.":" .""._WEBSITE.":"; echo"Step3,click'SaveOffice'." ."" ."" ."" .""; } CloseTable(); That form above allwos users to check a radio button (only one of 8 can be checked) for a location and then they can input the office info (address, phone, etc) and this is the "add-office" function: PHP Code: functionsaveoffice($user_id,$username,$storynum,$ublockon,$ublock,$broadcast,$oid,$practname,$add1,$add2,$city,$state,$zip,$phone,$fax,$website){ global$user,$cookie,$userinfo,$user_prefix,$db,$module_name; cookiedecode($user); $check=$cookie[1]; $check2=$cookie[2]; $sql="SELECTuser_id,user_passwordFROM".$user_prefix."_usersWHEREusername='$check'"; $result=$db->sql_query($sql); $row=$db->sql_fetchrow($result); $vuid=intval($row['user_id']); $ccpass=$row['user_password']; if(($user_id==$vuid)AND($check2==$ccpass)){ if(isset($ublockon))$ublockon=1;else$ublockon=0; $ublock=FixQuotes($ublock); $db->sql_query("UPDATEofficesSEToid='$oid',locationid='$locationid',offname='$offname',add1='$add1',add2='$add2',city='$city',state='$state',zip='$zip',phone='$phone',fax='$fax',website='$website',WHEREuser_id='$user_id'"); getusrinfo($user); docookie($userinfo['user_id'],$userinfo['username'],$userinfo['user_password'],$userinfo['storynum'],$userinfo['umode'],$userinfo['uorder'],$userinfo['thold'],$userinfo['noscore'],$userinfo['ublockon'],$userinfo['theme'],$userinfo['commentmax']); Header("Location:modules.phpname=$module_name"); } } Sorry for the long post but can anyone see any mistakes My site comes up OK, and redirects when I click on the "Add Office" button, but nothign goes into the DB andI get no errors. PS- this is a phpNuke site
On duplicate key Update did not work   (467 Views)
Hi.. I encountered problem in using on duplicate key update.. here is my query: Code: INSERT INTO parameter_settings (P27, P27_max, P27LOT_max, P27_maxdoz, P27Doz_max, P27_min, P27LOT_min, P27_mindoz, P27Doz_min, P28, P28_max, P28LOT_max, P28_maxdoz, P28Doz_max, P28_min, P28LOT_min, P28_mindoz, P28Doz_min, P30, P30_max, P30LOT_max, P30_maxdoz, P30Doz_max, P30_min, P30LOT_min, P30_mindoz, P30Doz_min, P32, P32_max, P32LOT_max, P32_maxdoz, P32Doz_max, P32_min, P32LOT_min, P32_mindoz, P32Doz_min, P32W, P32W_max, P32WLOT_max, P32W_maxdoz, P32WDoz_max, P32W_min, P32WLOT_min, P32W_mindoz, P32WDoz_min, P33, P33_max, P33LOT_max, P33_maxdoz, P33Doz_max, P33_min, P33LOT_min, P33_mindoz, P33Doz_min, P35, P35_max, P35LOT_max, P35_maxdoz, P35Doz_max, P35_min, P35LOT_min, P35_mindoz, P35Doz_min, P35M, P35M_max, P35MLOT_max, P35M_maxdoz, P35MDoz_max, P35M_min, P35MLOT_min, P35M_mindoz, P35MDoz_min, P35W, P35W_max, P35WLOT_max, P35W_maxdoz, P35WDoz_max, P35W_min, P35WLOT_min, P35W_mindoz, P35WDoz_min, P38, P38_max, P38LOT_max, P38_maxdoz, P38Doz_max, P38_min, P38LOT_min, P38_mindoz, P38Doz_min, P41, P41_max, P41LOT_max, P41_maxdoz, P41Doz_max, P41_min, P41LOT_min, P41_mindoz, P41Doz_min, P42, P42_max, P42LOT_max, P42_maxdoz, P42Doz_max, P42_min, P42LOT_min, P42_mindoz, P42Doz_min, P43, P43_max, P43LOT_max, P43_maxdoz, P43Doz_max, P43_min, P43LOT_min, P43_mindoz, P43Doz_min, P45, P45_max, P45LOT_max, P45_maxdoz, P45Doz_max, P45_min, P45LOT_min, P45_mindoz, P45Doz_min, P46, P46_max, P46LOT_max, P46_maxdoz, P46Doz_max, P46_min, P46LOT_min, P46_mindoz, P46Doz_min, P47, P47_max, P47LOT_max, P47_maxdoz, P47Doz_max, P47_min, P47LOT_min, P47_mindoz, P47Doz_min, Total, Total_max, TotalLOT_max, Total_maxdoz, TotalDoz_max, Total_min, TotalLOT_min, Total_mindoz, TotalDoz_min ) VALUES ('$P27', '$P27_max', '$P27LOT_max', '$P27_maxdoz', '$P27Doz_max', '$P27_min', '$P27LOT_min', '$P27_mindoz', '$P27Doz_min', '$P28', '$P28_max', '$P28LOT_max', '$P28_maxdoz', '$P28Doz_max', '$P28_min', '$P28LOT_min', '$P28_mindoz', '$P28Doz_min', '$P30', '$P30_max', '$P30LOT_max', '$P30_maxdoz', '$P30Doz_max', '$P30_min', '$P30LOT_min', '$P30_mindoz', '$P30Doz_min', '$P32', '$P32_max', '$P32LOT_max', '$P32_maxdoz', '$P32Doz_max', '$P32_min', '$P32LOT_min', '$P32_mindoz', '$P32Doz_min', '$P32W', '$P32W_max', '$P32WLOT_max', '$P32W_maxdoz', '$P32WDoz_max', '$P32W_min', '$P32WLOT_min', '$P32W_mindoz', '$P32WDoz_min', '$P33', '$P33_max', '$P33LOT_max', '$P33_maxdoz', '$P33Doz_max', '$P33_min', '$P33LOT_min', '$P33_mindoz', '$P33Doz_min', '$P35', '$P35_max', '$P35LOT_max', '$P35_maxdoz', '$P35Doz_max', '$P35_min', '$P35LOT_min', '$P35_mindoz', '$P35Doz_min', '$P35M', '$P35M_max', '$P35MLOT_max', '$P35M_maxdoz', '$P35MDoz_max', '$P35M_min', '$P35MLOT_min', '$P35M_mindoz', '$P35MDoz_min', '$P35W', '$P35W_max', '$P35WLOT_max', '$P35W_maxdoz', '$P35WDoz_max', '$P35W_min', '$P35WLOT_min', '$P35W_mindoz', '$P35WDoz_min', '$P38', '$P38_max', '$P38LOT_max', '$P38_maxdoz', '$P38Doz_max', '$P38_min', '$P38LOT_min', '$P38_mindoz', '$P38Doz_min', '$P41', '$P41_max', '$P41LOT_max', '$P41_maxdoz', '$P41Doz_max', '$P41_min', '$P41LOT_min', '$P41_mindoz', '$P41Doz_min', '$P42', '$P42_max', '$P42LOT_max', '$P42_maxdoz', '$P42Doz_max', '$P42_min', '$P42LOT_min', '$P42_mindoz', '$P42Doz_min', '$P43', '$P43_max', '$P43LOT_max', '$P43_maxdoz', '$P43Doz_max', '$P43_min', '$P43LOT_min', '$P43_mindoz', '$P43Doz_min', '$P45', '$P45_max', '$P45LOT_max', '$P45_maxdoz', '$P45Doz_max', '$P45_min', '$P45LOT_min', '$P45_mindoz', '$P45Doz_min', '$P46', '$P46_max', '$P46LOT_max', '$P46_maxdoz', '$P46Doz_max', '$P46_min', '$P46LOT_min', '$P46_mindoz', '$P46Doz_min', '$P47', '$P47_max', '$P47LOT_max', '$P47_maxdoz', '$P47Doz_max', '$P47_min', '$P47LOT_min', '$P47_mindoz', '$P47Doz_min', '$Total', '$Total_max', '$TotalLOT_max', '$Total_maxdoz', '$TotalDoz_max', '$Total_min', '$TotalLOT_min', '$Total_mindoz', '$TotalDoz_min' ) ON DUPLICATE KEY UPDATE P27 = '$P27', P27_max = '$P27_max', P27LOT_max = '$P27LOT_max', P27_maxdoz = '$P27_maxdoz', P27Doz_max = '$P27Doz_max', P27_min = '$P27_min', P27LOT_min = '$P27LOT_min', P27_mindoz = '$P27_mindoz', P27Doz_min = '$P27Doz_min', P28 = '$P28', P28_max = '$P28_max', P28LOT_max = '$P28LOT_max', P28_maxdoz = '$P28_maxdoz', P28Doz_max = '$P28Doz_max', P28_min = '$P28_min', P28LOT_min = '$P28LOT_min', P28_mindoz = '$P28_mindoz', P28Doz_min = '$P28Doz_min', P30 = '$P30', P30_max = '$P30_max', P30LOT_max = '$P30LOT_max', P30_maxdoz = '$P30_maxdoz', P30Doz_max = '$P30Doz_max', P30_min = '$P30_min', P30LOT_min = '$P30LOT_min', P30_mindoz = '$P30_mindoz', P30Doz_min = '$P30Doz_min', P32 = '$P32', P32_max = '$P32_max', P32LOT_max = '$P32LOT_max', P32_maxdoz = '$P32_maxdoz', P32Doz_max = '$P32Doz_max', P32_min = '$P32_min', P32LOT_min = '$P32LOT_min', P32_mindoz = '$P32_mindoz', P32Doz_min = '$P32Doz_min', P32W = '$P32W', P32W_max = '$P32W_max', P32WLOT_max = '$P32WLOT_max', P32W_maxdoz = '$P32W_maxdoz', P32WDoz_max = '$P32WDoz_max', P32W_min = '$P32W_min', P32WLOT_min = '$P32WLOT_min', P32W_mindoz = '$P32W_mindoz', P32WDoz_min = '$P32WDoz_min', P33 = '$P33', P33_max = '$P33_max', P33LOT_max = '$P33LOT_max', P33_maxdoz = '$P33_maxdoz', P33Doz_max = '$P33Doz_max', P33_min = '$P33_min', P33LOT_min = '$P33LOT_min', P33_mindoz = '$P33_mindoz', P33Doz_min = '$P33Doz_min', P35 = '$P35', P35_max = '$P35_max', P35LOT_max = '$P35LOT_max', P35_maxdoz = '$P35_maxdoz', P35Doz_max = '$P35Doz_max', P35_min = '$P35_min', P35LOT_min = '$P35LOT_min', P35_mindoz = '$P35_mindoz', P35Doz_min = '$P35Doz_min', P35M = '$P35M', P35M_max = '$P35M_max', P35MLOT_max = '$P35MLOT_max', P35M_maxdoz = '$P35M_maxdoz', P35MDoz_max = '$P35MDoz_max', P35M_min = '$P35M_min', P35MLOT_min = '$P35MLOT_min', P35M_mindoz = '$P35M_mindoz', P35MDoz_min = '$P35MDoz_min', P35W = '$P35W', P35W_max = '$P35W_max', P35WLOT_max = '$P35WLOT_max', P35W_maxdoz = '$P35W_maxdoz', P35WDoz_max = '$P35WDoz_max', P35W_min = '$P35W_min', P35WLOT_min = '$P35WLOT_min', P35W_mindoz = '$P35W_mindoz', P35WDoz_min = '$P35WDoz_min', P38 = '$P38', P38_max = '$P38_max', P38LOT_max = '$P38LOT_max', P38_maxdoz = '$P38_maxdoz', P38Doz_max = '$P38Doz_max', P38_min = '$P38_min', P38LOT_min = '$P38LOT_min', P38_mindoz = '$P38_mindoz', P38Doz_min = '$P38Doz_min', P41 = '$P41', P41_max = '$P41_max', P41LOT_max = '$P41LOT_max', P41_maxdoz = '$P41_maxdoz', P41Doz_max = '$P41Doz_max', P41_min = '$P41_min', P41LOT_min = '$P41LOT_min', P41_mindoz = '$P41_mindoz', P41Doz_min = '$P41Doz_min', P42 = '$P42', P42_max = '$P42_max', P42LOT_max = '$P42LOT_max', P42_maxdoz = '$P42_maxdoz', P42Doz_max = '$P42Doz_max', P42_min = '$P42_min', P42LOT_min = '$P42LOT_min', P42_mindoz = '$P42_mindoz', P42Doz_min = '$P42Doz_min', P43 = '$P43', P43_max = '$P43_max', P43LOT_max = '$P43LOT_max', P43_maxdoz = '$P43_maxdoz', P43Doz_max = '$P43Doz_max', P43_min = '$P43_min', P43LOT_min = '$P43LOT_min', P43_mindoz = '$P43_mindoz', P43Doz_min = '$P43Doz_min', P45 = '$P45', P45_max = '$P45_max', P45LOT_max = '$P45LOT_max', P45_maxdoz = '$P45_maxdoz', P45Doz_max = '$P45Doz_max', P45_min = '$P45_min', P45LOT_min = '$P45LOT_min', P45_mindoz = '$P45_mindoz', P45Doz_min = '$P45Doz_min', P46 = '$P46', P46_max = '$P46_max', P46LOT_max = '$P46LOT_max', P46_maxdoz = '$P46_maxdoz', P46Doz_max = '$P46Doz_max', P46_min = '$P46_min', P46LOT_min = '$P46LOT_min', P46_mindoz = '$P46_mindoz', P46Doz_min = '$P46Doz_min', P47 = '$P47', P47_max = '$P47_max', P47LOT_max = '$P47LOT_max', P47_maxdoz = '$P47_maxdoz', P47Doz_max = '$P47Doz_max', P47_min = '$P47_min', P47LOT_min = '$P47LOT_min', P47_mindoz = '$P47_mindoz', P47Doz_min = '$P47Doz_min', Total = '$Total', Total_max = '$Total_max', TotalLOT_max = '$TotalLOT_max', Total_maxdoz = '$Total_maxdoz', TotalDoz_max = '$TotalDoz_max', Total_min = '$Total_min', TotalLOT_min = '$TotalLOT_min', Total_mindoz = '$Total_mindoz', TotalDoz_min = '$TotalDoz_min' I want to happen is only one row will add in my database and if I need to edit my data It will update the data that already been save. But in my code instead of updating my data in my database, it will insert or again in another row. I cant figure out whats wrong or missing in my query syntax. Thank you Thank you
avoid entering duplicate records through procedure   (177 Views)
Hi I want to insert the record into a table after checking that the record is not available in the table. below is the code:- CREATE PROCEDURE newprcSaveGPSLocation( _lat VARCHAR(45), _lng VARCHAR(45), _mph VARCHAR(45), _direction VARCHAR(45), _distance VARCHAR(45), _date VARCHAR(100), _locationMethod VARCHAR(100), _phoneNumber VARCHAR(20), _sessionID VARCHAR(50), _accuracy VARCHAR(20), _locationIsValid VARCHAR(5), _extraInfo VARCHAR(255), _recid VARCHAR(25) ) BEGIN DECLARE l_count INTEGER; select count(*) into l_count from locations where record_id ='_recid'; IF l_count
avoiding duplicates   (180 Views)
I have two mysql tables that are linked: subscribers, and subscribers_data. subscribers holds personal information about a subscriber, subscriber_data holds the technical info. the pk and fk are the subscriber's id. I'm having an issue while inserting email addresses into the subscribers_data table. I build a query and insert new email addresses into the subscribers table. I then want to take those inserted id numbers and insert them into the subscribers_data table. If I try to insert duplicate email addresses, they are ignored by the subscriber table (as they should be), however, the subscribers_data table continues with INSERTs. For instance. If I add two email addresses, then the subscribers table will now have 2, and the subscribers_data will have 2. Now, If I attempt to add those exact same email addresses, the subscribers table will reject them, but the subscribers_data table will now have 4 rows. It should reject them too. I'm just not sure how to do that. I tried foreach() and while() loops, but kept getting the same results. Code MySQL: CREATE TABLE IF NOT EXISTS `subscribers` ( `id` int(10) unsigned NOT NULL auto_increment, `fname` varchar(30) collate utf8_unicode_ci NOT NULL, `lname` varchar(30) collate utf8_unicode_ci NOT NULL, `email` varchar(60) collate utf8_unicode_ci NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `email` (`email`), ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Subscriber information' ; CREATE TABLE IF NOT EXISTS `subscribers_data` ( `sd_sub_id` int(10) unsigned NOT NULL, `sd_date_added` datetime NOT NULL default '0000-00-00 00:00:00', `sd_active` char(1) collate utf8_unicode_ci NOT NULL default '0', `sd_verified` char(1) collate utf8_unicode_ci NOT NULL default '0', PRIMARY KEY (`sd_sub_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='additional subscriber info'; PHP Code: //counteachPOSTedemailaddress $insert_count=0; $insert_id=get_max_sub_id()+1;//subscriberidneedstostartathighestid+1 $ok=array(); foreach($emailsas$email){ if(check_email_address($email)){ $ok[]="($insert_id,'$email')"; //checkforduplicateemailaddresses $email_check=email_check($email); if($email_check>0){ //emailaddressalreadyexists! $dup_error=1; $dup_email[]=$email; $dup_count=count($dup_email); } $insert_count++; $insert_id++; $new_email=$email; }else{ //errorstuff } } //createtemptableforinsertingnewemailaddresses $sql=mysql_query("CREATETEMPORARYTABLEsubscribers_temp(idINT(10),emailVARCHAR(60))TYPE=HEAP"; if(!empty($ok)){ $insert_query="INSERTINTOsubscribers_temp(id,email)VALUES".join(',',$ok); //insertnewemailaddressesintosubscribers_temptable $sql=mysql_query($insert_query)ordie(mysql_error()); } //getemailaddressesfromsubscribers_temptableandinsertintosubscriberstable $sql=mysql_query("INSERTIGNOREsubscribers(id,email)SELECTid,emailFROMsubscribers_temp"); //now,insertsubscriber'sidnumberandadditionaldataintosubscribersdatatable $query=mysql_query(" INSERTINTOsubscribers_data( sd_sub_id, sd_added_by, sd_date_added, sd_active, sd_verified )SELECTid,'a',now(),1,1FROMsubscribers_temp");
How can I remove this ?no-duplicate? constrict ion?   (189 Views)
I got this error massage: Duplicate entry '135' for key 1 SQL= INSERT INTO mos_comprofiler ( `id`,`user_id`,`approved` VALUES ( '135','135','1','1',' Its ok with me if id=id_user, How can I remove this no-duplicate constrict ion