asked on

MySQL > INSERT IGNORE INTO > Only inserts one row

Why is this query only inserting one row?

CREATE TABLE search_terms_new  ( 
	search_terms_id	mediumint(9) NOT NULL,
	search_terms   	varchar(255) NOT NULL,
	PRIMARY KEY(search_terms_id)
)
ENGINE = InnoDB
AUTO_INCREMENT = 0
GO
ALTER TABLE search_terms_new
	ADD CONSTRAINT the_unique
	UNIQUE (search_terms)
GO

Open in new window

INSERT IGNORE INTO server_logs.search_terms_new (search_terms_id,search_terms)
SELECT
    LAST_INSERT_ID()+1,
    search_terms
FROM
    server_logs.search_terms

Open in new window

Result:

 1 record(s) affected 

 [Executed: 10/28/2013 4:35:49 PM] [Execution: 5s]

Open in new window

If I run the query again, I get:

 0 record(s) affected 

 [Executed: 10/28/2013 4:36:25 PM] [Execution: 4s]

Open in new window

ASKER CERTIFIED SOLUTION

chaau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Chris Stanyon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Geoff Millikan

ASKER

Computers are so dang picky. Why can't they just do what I want, instead of exactly what I tell them to do? I mean really, who ever says EXACTLY what they want all the time? These machines need to stop being so literal.

Geoff Millikan

ASKER

Thanks so much, that fixed it!

MySQL &gt; INSERT IGNORE INTO &gt; Only inserts one row

MySQL > INSERT IGNORE INTO > Only inserts one row