tat2nu Posted November 23, 2010 Share Posted November 23, 2010 I read the READ THESE GUIDELINES Sticky and and am sorry if this does not meet your requirements or is in the wrong area but I have no idea on where to look for help with this DB issue and it looks like you guys are helpful and friendly. Here is what I have: I have a DB called "_xcart" that has the wrong/missing "city" and "zipcode" fields in the "address_book" table. I also have another DB with the same structure called "_2xcart2" that has the wrong "id" and "userid" fields but the "city" and "zipcode" fields are correct in the "address_book" table. Both of the DB's are on the same server and the "_xcart" DB is my current live store. I am wanting to to retrieve the "city" and "zipcode" field from DB "_2xcart2" rows where the "firstname", "lastname", "address", "state" and "country" fields are the same and overwrite those "city" and "zipcode" fields in the corresponding rows that are wrong/missing in DB "_xcart" Does this make sense? Can it be done? Is it something that I can do in phpMyAdmin or do I need to get a professional to help me? Thanks, Marc Quote Link to comment Share on other sites More sharing options...
ManiacDan Posted November 23, 2010 Share Posted November 23, 2010 Create a new table called _xcart.table3. It will be temporary. INSERT INTO _xcart.`table3` (SELECT _xcart.address_book.id, _xcart.address_book.userid, _xcart.address_book.firstname, _xcart.address_book.lastname, _xcart.address_book.address, _2xcart2.address_book.city, _xcart.address_book.state, _2xcart2.address_book.zipcode, _xcart.address_book.country FROM _xcart.address_book JOIN _2xcart2.address_book ON _xcart.address_book.firstname = _2xcart2.address_book.firstname AND _xcart.address_book.lastname = _2xcart2.address_book.lastname AND _xcart.address_book.address = _2xcart2.address_book.address AND _xcart.address_book.state = _2xcart2.address_book.state AND _xcart.address_book.country = _2xcart2.address_book.country) Then, once you verify the data is correct, overwrite the old table with the new one. -Dan Quote Link to comment Share on other sites More sharing options...
tat2nu Posted November 23, 2010 Author Share Posted November 23, 2010 Wow, that was fast. Thanks for the quick reply. There are other fields in the table also, this is the complete list: id userid default_s default_b title firstname lastname address city county state country zipcode phone fax Does this make a difference in the script you just wrote? Thanks, Marc Quote Link to comment Share on other sites More sharing options...
tat2nu Posted November 23, 2010 Author Share Posted November 23, 2010 OK, I modified your script to include the other fields, I also had to correct the DB and table names because I omitted the leading parts thinking they were not critical. I changed DB names: _xcart to needleji_xcart, and _2xcart2 to needleji_2xcart2 and I also had to change the table name to xcart_address_book from address_book. I guess that I thought the leading part referred to the hierarchy and was not important. Here is what I came up with. Please let me know what I did wrong because I am getting a syntax error at the FROM line. I will attach an image so you can see the DB and table names as they appear in phpMyAdmin. INSERT INTO needleji_xcart.`table3` (SELECT needleji_xcart.xcart_xcart_address_book.id, needleji_xcart.xcart_xcart_address_book.userid, needleji_xcart.xcart_address_book.default_s, needleji_xcart.xcart_address_book.default_b, needleji_xcart.xcart_address_book.title, needleji_xcart.xcart_address_book.firstname, needleji_xcart.xcart_address_book.lastname, needleji_xcart.xcart_address_book.address, needleji_2xcart2.xcart_address_book.city, needleji_xcart.xcart_address_book.state, needleji_xcart.xcart_address_book.county, needleji_2xcart2.xcart_address_book.country, needleji_xcart.xcart_address_book.zipcode, needleji_xcart.xcart_address_book.phone, needleji_xcart.xcart_address_book.fax, FROM needleji_xcart.xcart_address_book JOIN needleji_2xcart2.xcart_address_book ON needleji_xcart.xcart_address_book.firstname = needleji_2xcart2.xcart_address_book.firstname AND needleji_xcart.xcart_address_book.lastname = needleji_2xcart2.xcart_address_book.lastname AND needleji_xcart.xcart_address_book.address = needleji_2xcart2.xcart_address_book.address AND needleji_xcart.xcart_address_book.state = needleji_2xcart2.xcart_address_book.state AND needleji_xcart.xcart_address_book.country = needleji_2xcart2.xcart_address_book.country) Thanks again, Marc [attachment deleted by admin] Quote Link to comment Share on other sites More sharing options...
ManiacDan Posted November 23, 2010 Share Posted November 23, 2010 You have a comma at the end of your SELECT list. Quote Link to comment Share on other sites More sharing options...
tat2nu Posted November 23, 2010 Author Share Posted November 23, 2010 Very good eye. OK, I had some extra stuff in there that I needed to delete (redundant "xcart_'s" but I cleaned them out also. I went to theSQL tab for the DB in phpMyAdmin and ran the script. It resulted in a "#1136 - Column count doesn't match value count at row 1" INSERT INTO needleji_xcart.`table3` (SELECT needleji_xcart.xcart_address_book.id, needleji_xcart.xcart_address_book.userid, needleji_xcart.xcart_address_book.default_s, needleji_xcart.xcart_address_book.default_b, needleji_xcart.xcart_address_book.title, needleji_xcart.xcart_address_book.firstname, needleji_xcart.xcart_address_book.lastname, needleji_xcart.xcart_address_book.address, needleji_2xcart2.xcart_address_book.city, needleji_xcart.xcart_address_book.state, needleji_xcart.xcart_address_book.county, needleji_2xcart2.xcart_address_book.country, needleji_xcart.xcart_address_book.zipcode, needleji_xcart.xcart_address_book.phone, needleji_xcart.xcart_address_book.fax FROM needleji_xcart.xcart_address_book JOIN needleji_2xcart2.xcart_address_book ON needleji_xcart.xcart_address_book.firstname = needleji_2xcart2.xcart_address_book.firstname AND needleji_xcart.xcart_address_book.lastname = needleji_2xcart2.xcart_address_book.lastname A[...] Can you tell me what that means? Thanks again, Marc Quote Link to comment Share on other sites More sharing options...
ManiacDan Posted November 23, 2010 Share Posted November 23, 2010 Specify which columns of the target table you're trying to insert into: INSERT INTO table1 (table1ColumnName1, table1ColumnName2) VALUES (SELECT col1, col2 FROM table2) -Dan Quote Link to comment Share on other sites More sharing options...
tat2nu Posted November 23, 2010 Author Share Posted November 23, 2010 I created a table with just one field thinking that the script would just set the fields up for me. I just dropped that table and created another table3 with the exact same fields and structure as the xcart_address_book table and then ran the script again. Now I got this error "#1062 - Duplicate entry '79' for key 1". Can you tell me what this means? Thanks again, Marc Quote Link to comment Share on other sites More sharing options...
ManiacDan Posted November 23, 2010 Share Posted November 23, 2010 Your SELECT query is producing multiple matches. You will have to SELECT DISTINCT, or remove the keys from your target table and delete the duplicates by hand. -Dan Quote Link to comment Share on other sites More sharing options...
tat2nu Posted November 23, 2010 Author Share Posted November 23, 2010 I really appreciate all your help and patience with me but I have to ask for more precise instructions. I really do not understand this in detail, I am only trying to repair problems that a shopping cart migration company caused with my data and do not seem competent to fix after a number of tries. Are you saying to replace the SELECT command with SELECT DISTINCT? My end result is to have a duplicate of the needleji_xcart.xcart_address_book table where the "city" and "zipcode" fields have been overwritten with the entries from the needleji_2xcart2.xcart_address_book table rows where the "firstname", "lastname", "address", "state" and "country" fields are identical. Thanks again, I can not tell you how much I appreciate the help. Marc Quote Link to comment Share on other sites More sharing options...
ManiacDan Posted November 23, 2010 Share Posted November 23, 2010 Basically your problem is that you have TWO entries for the same address, so when you do your SELECT then you get multiple entries for the same ID, and you cannot enter multiple entries for the same ID into your target table because you have a primary key set up. So the solution is to replace SELECT with SELECT DISTINCT, or SELECT DISTINCT(needleji_xcart.xcart_address_book.id). That should clear out the duplicates. -Dan Quote Link to comment Share on other sites More sharing options...
tat2nu Posted November 23, 2010 Author Share Posted November 23, 2010 I tried replacing SELECT with SELECT DISTINCT and ended up with "#1062 - Duplicate entry '96' for key 1" after it ran about 23 rows. On to number 2... I tried this but got another "#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.xcart_address_book.id, needleji_xcart.xcart_address_book.userid, needleji_xca' at line 1" INSERT INTO needleji_xcart.`table3` (SELECT DISTINCT(needleji_xcart.xcart_address_book.id) needleji_xcart.xcart_address_book.id, needleji_xcart.xcart_address_book.userid, needleji_xcart.xcart_address_book.default_s, needleji_xcart.xcart_address_book.default_b, needleji_xcart.xcart_address_book.title, needleji_xcart.xcart_address_book.firstname, needleji_xcart.xcart_address_book.lastname, needleji_xcart.xcart_address_book.address, needleji_2xcart2.xcart_address_book.city, needleji_xcart.xcart_address_book.state, needleji_xcart.xcart_address_book.county, needleji_2xcart2.xcart_address_book.country, needleji_xcart.xcart_address_book.zipcode, needleji_xcart.xcart_address_book.zip4, needleji_xcart.xcart_address_book.phone, needleji_xcart.xcart_address_book.fax FROM needleji_xcart.xcart_address_book JOIN needleji_2xcart2.xcart_address_book ON needleji_xcart.xcart_address_book.firstname = needleji_2xcart2.xcart_address_book.firstname AND needleji_xcart.xcart_address_book.lastname = needleji_2xcart2.xcart_address_book.lastname AND needleji_xcart.xcart_address_book.address = needleji_2xcart2.xcart_address_book.address AND needleji_xcart.xcart_address_book.state = needleji_2xcart2.xcart_address_book.state AND needleji_xcart.xcart_address_book.country = needleji_2xcart2.xcart_address_book.country) Thanks again, Marc Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.