Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 2 of 2 FirstFirst 12
Results 16 to 20 of 20
  1. #16
    New Coder
    Join Date
    Mar 2013
    Posts
    10
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thank you my friend, I ran the command, here is the text:

    CREATE TABLE `jos_students_contacts` (
    `id` int(11) NOT NULL DEFAULT '0',
    `product_id` int(11) NOT NULL,
    `user_id` int(5) NOT NULL,
    `date` datetime NOT NULL,
    `name` varchar(255) NOT NULL,
    `email` varchar(255) NOT NULL,
    `phone` varchar(100) NOT NULL,
    `address` varchar(255) NOT NULL,
    `city` varchar(255) NOT NULL,
    `state` varchar(255) NOT NULL,
    `cp` varchar(100) NOT NULL,
    `text` text NOT NULL,
    `userfile` varchar(255) NOT NULL,
    `layout` varchar(255) NOT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8

  2. #17
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,121
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    WOW! You are right! You have *NO* INDEXES!

    How do you manage to prevent duplicate INSERTs even into that one table???

    Well, my remarks still stand.

    Is your other table identical? That is, does it have the same field names and types? If not, maybe you need to use SHOW CREATE TABLE on it as well?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  3. Users who have thanked Old Pedant for this post:

    lorenzofln (03-22-2013)

  4. #18
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,960
    Thanks
    120
    Thanked 76 Times in 76 Posts
    To hopefuly clarify to you some things.

    to merge two tables with any chance of good result:

    1. they have to have identical structure
    2. merged table must have the same structure as the source ones
    3.Imagine you have only one field in both source tables, and only one record and contence of it is in both 'john doe'

    4. what do you expect in merged table ?

    'john doe'
    'john doe'

    or just

    'john doe'

    ?

    The second option would be choice for most people on this forum.
    Hence the merged table has to have index !
    In order that insert ignore command would work --> prevent duplictaes of 'john doe' in merged table.

    And as you showed, there is no key/indexes in your source table. If you have a table with 1000 and one with 200, they should merge with no prob into 1200 records table.
    Of course there will be a mess with id-s, ....

    There, I told you same as old pedant, from another angle, hope it helps.
    Last edited by BubikolRamios; 03-22-2013 at 03:31 AM.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  5. Users who have thanked BubikolRamios for this post:

    lorenzofln (03-22-2013)

  6. #19
    New Coder
    Join Date
    Mar 2013
    Posts
    10
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by BubikolRamios View Post
    To hopefuly clarify to you some things.

    to merge two tables with any chance of good result:

    1. they have to have identical structure
    2. merged table must have the same structure as the source ones
    3.Imagine you have only one field in both source tables, and only one record and contence of it is in both 'john doe'

    4. what do you expect in merged table ?

    'john doe'
    'john doe'

    or just

    'john doe'

    ?

    The second option would be choice for most people on this forum.
    Hence the merged table has to have index !
    In order that insert ignore command would work --> prevent duplictaes of 'john doe' in merged table.

    And as you showed, there is no key/indexes in your source table. If you have a table with 1000 and one with 200, they should merge with no prob into 1200 records table.
    Of course there will be a mess with id-s, ....

    There, I told you same as old pedant, from another angle, hope it helps.
    Hello my friend. Sorry for the delay, my daughter went to hospital, but everything is good now..

    Look, the tables have identical structure, and in merged table i just want the id to be changed to a unique id, because it doesnt matter for me if the field name has 2 identical value, because it could be related to 2 different leads, i mean that i would like to have the row removed only if ALL FIELDS are identical, it does not matter if only one of them is identical, i want the duplicate to be remove only if all fields are identical, and i need a unique id per row.

    when i am talking about removing duplicates, the issue is that i will have to run this merging query everyday and i will need to process only new items, new leads only, to have only 1 table with all leads.. so i expect it to be

    john doe lead 1
    john doe lead 2

    but if ALL THE FIELDS are identical, then i expect to have only

    john doe

    did you understand my problem now? many thanks, really.
    Last edited by lorenzofln; 03-22-2013 at 02:05 PM.

  7. #20
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,960
    Thanks
    120
    Thanked 76 Times in 76 Posts
    well, i do know what your problem is exactly, all the time, as old pedant do, do nnt doubt of that at all (-: But you are forcing it all the time in the wrong way. Wrong way is not having any index on source tables, and you don't want to hear that or you don't understand what index is all about.

    This is how your merged table should be:
    Code:
    CREATE TABLE `merged` (
    	`id` INT(11) NOT NULL AUTO_INCREMENT,
    	`product_id` INT(11) NOT NULL,
    	`user_id` INT(5) NOT NULL,
    	`date` DATETIME NOT NULL,
    	`name` VARCHAR(255) NOT NULL,
    	`email` VARCHAR(255) NOT NULL,
    	`phone` VARCHAR(100) NOT NULL,
    	`address` VARCHAR(255) NOT NULL,
    	`city` VARCHAR(255) NOT NULL,
    	`state` VARCHAR(255) NOT NULL,
    	`cp` VARCHAR(100) NOT NULL,
    	`text` TEXT NOT NULL,
    	`userfile` VARCHAR(255) NOT NULL,
    	`layout` VARCHAR(255) NOT NULL,
    	UNIQUE INDEX `Index 1` (`id`)
    )
    COLLATE='utf8_general_ci'
    ENGINE=MyISAM;

    As you described it in last post , merged table shoud have key on all fields, and since tere are so many fields that is bad all together, furthermore mysql will not let you do that:

    forinstance, this:
    Code:
    ALTER TABLE `merged`
    	ADD INDEX `Index 2` (`address`, `city`, `state`, `phone`, `product_id`, `date`, `cp`, `text`(100), `userfile`, `name`, `layout`, `user_id`, `email`);
    will pop up:
    SQL Error (1071): Specified key was too long; max key length is 1000 bytes
    So to be able to make key ona all fields you should try to downsize them, but ,for sure ,that will lead to nowhere.

    Without key,you can do it programticaly with application, but that would be 'super dumb'.

    bottom line, decide which fields are necessary for key, if I ignore all the rest.

    Not to mention, the table of yours should be splitted into two tables at least,
    I mean what is product_id doing in students table at all .
    Last edited by BubikolRamios; 03-22-2013 at 02:42 PM.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search


 
Page 2 of 2 FirstFirst 12

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •