PLEASE HELP with SQL Query should be simple but missing something!

[kamlooper]

I have the following to take data from one table and add it to a seperate table.  It all works unless I have the data already in the table I am adding to.  So what I wanted to do was add the first where statement to check the data prior to attempting to add it, but it does not seem to make a difference. 

 

Can someone help me with the clauses or statements i need to cross check the data from the two tables so that it does not error out if I have the data already in the table I am adding to.

 

INSERT INTO wp_term_relationships (object_id, term_taxonomy_id, term_order)

 

SELECT ID, 27, 0 FROM wp_posts

WHERE wp_term_relationships.object_id = wp_posts.ID

AND wp_term_relationships.term_taxonomy_id <> 27

 

AND WHERE post_title

LIKE '%future%'

AND post_status = 'publish'

OR post_title LIKE '%option%'

AND post_content LIKE '%fundamental%'

AND post_status = 'publish'

[Adam]

You can't have multiple WHERE clauses within a query. How are you running it? If you start an INSERT statement you need to insert some data, otherwise you will get an error. Depending upon the application/environment in which you run it though, you could actually handle the error instead of trying to avoid it. For example, when you try to insert a duplicate row (violating a unique or primary key), you will get a 1062 error. You can capture that error code and display a suitable message instead.

[kamlooper]

I have no problem with that option.  I am running this in php, ideally trying to get it to the point so it can auto load the php page in a cron job, and if it errors and continues so that any new data is added, that would be totally fine by me, so long as applicable data is still inserted if it errors. 

 

How would I do that though? 

 

 

[kickstart]

Hi

 

You can use the ON DUPLICATE KEY type syntax to cope with it (saves you doing the SELECT first).

 

However what do you want to do with the records should it already be there?

 

All the best

 

Keith

[kamlooper]

I can give this a try, the only question i have with that is, there will be duplicate ID's, just want to avoid it erroring out when i have data that will be duplicate's in two columns.  I am taking data from one table, inserting it into another table with hardcoded category numbers, its when the hardcoded category number and ID's both already exist that I need it to basically say F'it and skip this one and move onto the next insert.

 

can you help me on this, I am a bit of a self tough hack.  So any direction you can provide would be HUGE!

[kickstart]

Hi

 

If you don't want anything to happen when the row already exists then you can just use INSERT IGNORE INTO ..........

 

If you want to update the rows when they already exist you can use INSERT.... ON DUPLICATE KEY UPDATE......

 

However not sure which you want to do.

 

All the best

 

Keith

[kamlooper]

THANKS ALL!

 

THE IGNORE COMMAND WORKED PERFECTLY!