SEARCH YOUR SOLUTION HERE  

On duplicate key Update did not work

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

Posted On: Thursday 25th of October 2012 10:00:54 PM Total Views:  375
View Complete with Replies




Related Messages:

removing duplicate entries that are LIKE   (189 Views)
I have a database with about 30K lines of data. I need to remove any duplicates but the problem is the duplicates may be similar but not exact. For example if I had a company called Company name, it could be in there twice as follows: Code: company | contact Company name, Inc. | Randy M Company name | Randy M I have tried to query each distinct company name and then loop results where company name is LIKE 'the disctinct name' but it isn't working as intended. Does anyone have any ideas on how to do this fairly easily I am programming in PHP.
Should I duplicate address details?   (178 Views)
I am creating an admin page to allow users to add details of rental properties One user can add more than one rental property therefore I have separated the data and used 2 tables in the database: 1. members 2. properties I am not sure what to do in the scenario where the address and telephone etc details are the same for the member and the rental property. In some situations this may not be the case. Therefore should I duplicate the data and store the details both in the members and the properties tables One problem with duplicating the details is if a member wants to change their details, they may need to do this twice. I would therefore appreciate advice on how best to deal with this If I do duplicate the data, is it possible to have a button or link to populate the form with the contact details of the member if the details of the rental property is the same as their own contact details At least this would prevent them having to type them again. Any advice much appreciated!!
MYSQL query, search for "latest" duplicates   (97 Views)
I have just imported 1000 products into a shopping cart which had 3000 products. About 100 of these newly imported products are duplicates of the old ones. I found out how to find these duplicates with the following query- select * from CubeCart_inventory group by productCode having count(*) > 1 My problem now, is that the query above lists only one of the duplicates, and that is the older one, the one that was already in the system. The import I did didnt have the same quality of data for each product as the earlier data so I want to be able to remove the duplicate that I imported today... if you get what I mean So how would I do the following - There is another column called ProductId and thats a sequential number, the products I entered today started at about 3000 and went up to 4000 as the value for ProductId. How can I add that to the query so it shows duplicates with productId > 3000 Seems to me this would be the best way to do it and I can manually delete the results from this query from there.
How do i duplicate a MySQL database?   (126 Views)
i need to do is duplicate the database and have it operable on the server under a different name. I dont want to create a new one because i need to retain the table structure... Whats the easiest way of doing this (I am a noob at this) Thanx for any help... BTW: I am on a Windows Server 2003...
finding duplicate names in a column   (297 Views)
hi I have 2 field in my table as id and name id Name 1 asd 2 asd 3 asd 4 acv 5 USD 6 USD 7 acx 8 zxc 9 xcv 10 ZXb I want to find out the duplicates names and their id and how many times a particular name duplicates is it possible please help me
Removing duplicates with unique IDs?   (190 Views)
I searched around and found a few threads similar to my problem, but I wasn't able to find a very clear answer to this question. Basically I have a huge database (8.2 m records) full of destinations, things like restaurants, hotels, etc, and there are a lot of duplicates. The duplicates are in the database from the source, and we cannot change that. Some duplicates actually have up to 3 columns that are different. The ID is unique for each row, and the category number can be different as well. I've even seen a couple instances where the zip code does not have the leading zero. Here are some example rows: ID_____NAME_____ADDRESS_____CITY_____STATE_____ZIP_____CAT 544 WAL-MART 1234 MAIN ST DALLAS TX 05424 1234 545 WAL-MART 1234 MAIN ST DALLAS TX 5424 4321 546 WAL-MART 1234 MAIN ST DALLAS TX 05424 1234 The reason why these duplicates exist (probably) is because one WAL-MART could fit into multiple categories, because it's a grocery store, a department store, a photo center, etc. I don't want all that junk, I just want 1 record for 1 Wal-Mart How could I go about deleting these duplicate records and if possible, I'd like to be able to choose which record is kept, based on the category ID.
delete all but one duplicate (was "Is there a MySQL command that does this?")   (138 Views)
My server recently crashed and I had to restore a database from two different backups I had on my computer (one up to August 2005, and the other from June until [almost] present). Because of the overlap, there are quite a few duplicate entries. Is there an easy way, through MySQL, for me to search for duplicate records and delete all-but-one-of-them The only method I'm aware of is copying all the data to a temporary table and then copying it back into the table, but the database is almost 200MB and I wanted to find out if there was an easier way to do so.
Order by field, which lists duplicate value?   (143 Views)
The following sql returns the 'tid' and 'pos' for only 4 out of the 5 passed in words - it misses out the duplicate OR between password and about: email OR password about What I actually want is it to return all 5 words in the same order as given to the sort by field function, so... email OR password OR about Does anyone know how you write this properly so that ALL 5 words are returned with their 'tid' and 'pos' Including the duplicate OR word which seems to get ignored
MySQL Query problem (duplicate results)   (319 Views)
there Im having a problem finding duplicate results in a mysql database (a cocktail recipe website). Here the setup: Table 1: 'cocktail' [cid,c_name] (cid = unique cocktail id, c_name = cocktail name) Table 2: 'ingredients': [iid,i_name] (iid = unique ingredient id, i_name = ingredient name) Table 3: 'cocktail_ingredients' (the linking table) [ciid,cid,iid] (ciid = unique row identifier, cid = cocktail cid, iid = ingredient iid) So one cocktail can have multiple rows in the 'cocktail_ingredients' table (1 to many). Setup is fine. The problem Im having now is finding if there are duplicate cocktails in my database. For instance if the cocktail_ingredients table had these entries: cid | iid 1 | 56 1 | 78 1 | 101 . . . 9 | 56 9 | 78 9 | 101 The cocktail is the same (for theoretical purposes here anyway). If the 'cocktail_ingredients' table had one more row ... 9 | 103 Then it wouldn't be the same, as cocktail number 9 includes an extra ingredient. So the mysql has to do 2 checks, firstly that the ingredient count is the same, and secondly that every ingredient id (iid) is the same for corresponding cocktails (cid). Im stumped on this one, any help much appreciated. I'm thinking I might have to head down the PHP route as well to code in something more complex, but I'm struggling there as well so thought this would be a good place to stop and ask.
Joining table to itself: Avoiding duplicate column combinations   (124 Views)
, I'm trying to avoid duplicate column combinations when joining a table to itself. For example, say may table looks like so: Code: name | coolness --------------- John | 5 Jenn | 8 Dave | 8 Rexy | 3 If I do the following query: Code: SELECT a.name as name1, b.name as name2 FROM tName as a INNER JOIN tName as b ON a.coolness = b.coolness WHERE a.name != b.name I get the following results: Code: name1 | name2 -------------- Jenn | Dave Dave | Jenn These 2 returned rows do not present distinct column combinations. How can I modify my query to get distinct combinations of the two columns (i.e. only one row would be returned in the example above, as such: Code: name1 | name2 -------------- Jenn | Dave
How can I allow for duplicate ids in the primary key field   (190 Views)
I have items that are being put in to my database. I can enter one item but then when I want to enter a new item I get a duplicate key error. The reason I need to have the same id for items is that the items correspond to clients and each client has different items. I thought I had this set up correctly but setting both of the fields in the DB table to be the primary keys but that is not working. Basically this is what my DB needs to look like. soq_references_id-------bullet ----------------1-------bullet 1 ----------------1-------bullet 2 ----------------1-------bullet 3 It will continue down the line with the different ids. The Ids are coming from a table called soq_references, so each soq_references_id relates to a clietns ID and puts the bullets under the right client. Cliff notes: I need to know how to allow for duplicate soq_references_id's. if you need more information please ask, thank you in advance for the help. , 9-bullet is just saying the id is "9" and the text being put in is "bullet". Im using CRUD so that's how it displays it.
How to prevent duplicate entries   (137 Views)
Hi I how can I prevent duplicate enteries in mysql database. I set slim browser to auto refresh every second, as I was hit n trying codes, learning my myself from online sites. suddenly i saw in phpmyadmin theat there were 44 duplicate entries already. Ho can i make an error appear if similar entry exists. PHP Code: PHP Code:
Selecting duplicate rows - Please HELP!   (119 Views)
I have a 3 column table: ID - unique key autoincrement field Name - varchar 50 Description - varchar 100 What I need to do is select and display all the duplicate rows based on the name field. I've seen how to do a count of the dupes, but can't figure out how to display all the dupes. The output should be something like this: ID Name Desc 1 Tom dept1 2 Tom dept22 7 Tom dept17 9 Dave dept11 10 Dave dept3 and so on. Can anyone please help a newbie with this suggestions are greatly appreciated!
Returning duplicate rows based on a column value   (127 Views)
I've got some data that looks like this: id, name, count 1, dave, 2 2, bill, 4 3, ted, 2 I need a select that returns: dave dave bill bill bill bill ted ted Can I do this in mysql I'm assuming I'd join a table to itself, but I don't know exactly how to return more rows than the original.
Concatenate multiple rows into a set list(no duplicates) based on unique ID   (132 Views)
all; Any help, directions, or thoughts on this is much apprecaited. Let's say I have the following table: Code: ID Value 1 a 1 a 1 b 2 c 2 a 3 a 3 c 3 b Is there a quick way in MySQL that will let me build a new table so that it is now represented where the ValueCombination is sorted such as: Code: ID ValueCombination 1 a 2 a,c 3 a,b,c After this is done can I build a table from this tabel for the counts associated to the Values in the ValueCombination field such as: Code: Value Count a 3 b 1 c 2 After this I would like to build a table again from the ValueCombination table to count the instances of the ValueCombination such as: Code: ValueCombination Count a 1 a,b,c 1 a,c 1 As mentioned any suggestions are much appreciated. Celeste.
Identifying duplicate rows across mulitple tables   (116 Views)
Several large tables linked by a common ID contain duplicate rows (across all tables). Is there a way to identify these rows with a SELECT query t1 id val 1 3.4 2 4.4 3 4.4 t2 id val 1 1.1 2 10.3 3 10.3 SELECT t1.id FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE ( ) Would return id 2 3
Duplicate records - how to not show duplicates?   (154 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...   (146 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   (131 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   (115 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.2.8.0.2. Any ideas on the appropriate table setup to do this, or will I have to write a PHP function to check for duplicates