update if row exists else insert new record using data from another table

Question

I'm sure this has been asked before but I can't quite make the connection from what I'm trying to do with what I have found searching. I think this is because I want to do it from a second table and with a non key field.

Basically I have this curriculum.keywords table which contains keywords for different courses.

+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int(11)      | NO   | PRI | NULL    | auto_increment |
| course_id | int(11)      | NO   | MUL | NULL    |                |
| keyword   | varchar(100) | NO   | MUL | NULL    |                |
| excerpt   | longtext     | NO   |     | NULL    |                |
| explained | longtext     | NO   |     | NULL    |                |
| slug      | varchar(100) | NO   |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+

and I have an extract from an old wordpress website which contains lots of theory notes with titles that match some of the keywords in the table above.

+------------------+----------------+------+-----+---------+-------+
| Field            | Type           | Null | Key | Default | Extra |
+------------------+----------------+------+-----+---------+-------+
| wp_ID            | int(4)         | YES  |     | NULL    |       |
| wp_post_date     | varchar(19)    | YES  |     | NULL    |       |
| wp_post_modified | varchar(19)    | YES  |     | NULL    |       |
| wp_post_status   | varchar(7)     | YES  |     | NULL    |       |
| wp_post_title    | varchar(35)    | YES  |     | NULL    |       |
| wp_post_content  | varchar(10435) | YES  |     | NULL    |       |
| wp_post_excerpt  | varchar(224)   | YES  |     | NULL    |       |
| wp_post_parent   | int(1)         | YES  |     | NULL    |       |
| wp_post_name     | varchar(33)    | YES  |     | NULL    |       |
| wp_post_type     | varchar(8)     | YES  |     | NULL    |       |
+------------------+----------------+------+-----+---------+-------+

If the keyword is found I want to update the excerpt and explained fields with the old wordpress data giving it a course id of 2. If the keyword does not exist I would like to insert a new row and give it a course_id of 4.

I started with this from here but cannot quite finish it off.

    IF NOT EXISTS (SELECT * FROM `curriculum.keywords` WHERE `keyword` = STUCK HERE) THEN

    INSERT INTO `curriculum.keywords` (`keyword`, `excerpt`, `explained`, `slug`, `course_id`) 
    SELECT `wp_post_title`, `wp_post_excerpt`, `wp_post_content`, `wp_post_name`, 4 FROM `aimport-notes`

    ELSE

    UPDATE `curriculum.keywords` (`keyword`, `excerpt`, `explained`, `slug`, `course_id`) 
    SELECT `wp_post_title`, `wp_post_excerpt`, `wp_post_content`, `wp_post_name`, 4 FROM `aimport-notes` 
   WHERE `curriculum.keywords`.`keyword` = `aimport-notes`.`wp_post_title`

    END IF;

Thanks in anticipation of your help.

Chris

What if a keyword is found in more than one WP records? – JimmyB Nov 06 '15 at 18:04 — JimmyB, Nov 06 '15 at 18:04

score 0 · Answer 1 · answered Nov 06 '15 at 18:01

0

IF NOT EXISTS (
    SELECT 1 FROM `curriculum.keywords` kw
    INNER JOIN `wordpress` wp
    ON wp.wp_post_title LIKE CONCAT( '%', kw.keyword, '%')
)
...

answered Nov 06 '15 at 18:01

JimmyB

12,101
2
28
44

score 0 · Accepted Answer · answered Nov 06 '15 at 18:21

Query to insert data with new keywords:

INSERT INTO `curriculum.keywords` (`keyword`, `excerpt`, `explained`, `slug`, `course_id`) 
SELECT notes.`wp_post_title`, notes.`wp_post_excerpt`, notes.`wp_post_content`, notes.`wp_post_name`, 4 
FROM `aimport-notes` notes left join `curriculum.keywords` keywords on
notes.keyword=keywords.wp_post_title where keywords.keyword is null;

query to update matched keyword records:

update `curriculum.keywords` keywords
inner join `aimport-notes` notes on notes.keyword=keywords.wp_post_title
set keywords.keyword=notes.wp_post_title,
    keywords.excerpt=notes.wp_post_excerpt,
    keywords.explained=notes.wp_post_content,
    keywords.slug=notes.wp_post_name,
    keywords.course_id=4;

@hanno-binder, your suggestion might be correct and you had a good idea about duplicate titles but I opted this Code-Monk approach as it was in two parts - easier for me to navigate. Apart from a couple of field name errors it worked just fine. Thanks for the Friday help guys. — Byte Insight, Nov 06 '15 at 21:19

update if row exists else insert new record using data from another table

2 Answers2