#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2006
    Posts
    13
    Rep Power
    0

    Insert Ignore Not Working


    Im trying to use insert ignore but its not working due to the auto increment on the item_property_id

    Code:
    	INSERT IGNORE INTO
    		item_properties
    		(
    			item_id,
    			property_id
    		)
    	SELECT
    		xgen_items.wh_item_id,
    		properties.property_id
    	FROM
    		xgen_items_x_mastercategories AS xixm
    	INNER JOIN
    		xgen_mastercategories AS xm
    	ON
    		xm.xgen_mastercategory_id = xixm.xgen_mastercategory_id
    	INNER JOIN
    		xgen_items
    	ON
    		xgen_items.itemid = xixm.xgen_item_id
    	INNER JOIN
    		properties
    	ON
    		properties.property = xm.mastercategory
    
    CREATE TABLE IF NOT EXISTS `item_properties` (
      `item_property_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
      `item_id` int(11) unsigned NOT NULL,
      `property_id` int(11) unsigned NOT NULL,
      `created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
      `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      `deleted` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
      PRIMARY KEY (`item_property_id`),
      KEY `item_id` (`item_id`),
      KEY `property_id` (`property_id`),
      KEY `item_prop_id` (`item_id`,`property_id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3599319 ;

    Any help would be much appreciated.

    Thanks
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2006
    Posts
    13
    Rep Power
    0
    After doing a little digging around here is what I came up with.

    Let me know what you think

    Code:
    	WHERE 
    	(
    		xgen_items.wh_item_id,
    		properties.property_id
    	)
    	NOT IN 
    	(
    	SELECT 
    		item_id,property_id
    	FROM
    		item_properties
    	)
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    i thought you couldn't insert into a table if you're also selecting from it, which you are with your NOT EXISTS check

    try changing your item_prop_id key to UNIQUE

    that way, you won't have to do the NOT EXISTS, the uniqueness will take care of that for you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,788
    Rep Power
    349
    Originally Posted by r937
    i thought you couldn't insert into a table if you're also selecting from it
    I think that obnoxious limitation only applies to DELETE and UPDATE statements.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

IMN logo majestic logo threadwatch logo seochat tools logo