Update multiple mysql rows with 1 query?

Question

I am porting client DB to new one with different post titles and rows ID's , but he wants to keep the hits from old website,

he has over 500 articles in new DB , and updating one is not an issue with this query

UPDATE blog_posts 
SET hits=8523 WHERE title LIKE '%slim charger%' AND category = 2

but how would I go by doing this for all 500 articles with 1 query ? I already have export query from old db with post title and hits so we could find the new ones easier

INSERT INTO `news_items` (`title`, `hits`) VALUES
('Slim charger- your new friend', 8523 )...

the only reference in both tables is product name word within the title everything else is different , id , full title ...

Conner · Accepted Answer · 2012-08-20 05:01:34Z

3

Make a tmp table for old data in old_posts

UPDATE new_posts LEFT JOIN old_posts ON new_posts.title = old_posts.title SET new_posts.hits = old_posts.hits;

edited Aug 20, 2012 at 5:01

Conner

31.4k8 gold badges55 silver badges75 bronze badges

answered Aug 20, 2012 at 4:53

fanlix

1,3781 gold badge16 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

awiebe · Accepted Answer · 2012-08-20 04:22:08Z

0

Unfortunately that's not how it works, you will have to write a script/program that does a loop.

articles cursor;
selection articlesTable%rowtype;
WHILE(FETCH(cursor into selection)%hasNext)
Insert into newTable selection;
END WHILE

How you bridge it is up to you, but that's the basic pseudo code/PLSQL.

The APIs for selecting from one DB and putting into another vary by DBMS, so you will need a common intermediate format. Basically take the record from the first DB, stick it into a struct in the programming language of your choice, and prefrom an insert using those struct values using the APIs for the other DBMS.

answered Aug 20, 2012 at 4:22

awiebe

3,8784 gold badges25 silver badges34 bronze badges

Comments

Jared Drake · Accepted Answer · 2012-08-20 04:27:41Z

0

I'm not 100% sure that you can update multiple records at once, but I think what you want to do is use a loop in combination with the update query.

However, if you have 2 tables with absolutely no relationship or common identifiers between them, you are kind of in a hard place. The hard place in this instance would mean you have to do them all manually :(

The last possible idea to save you is that the id's might be different, but they might still have the same order. If that is the case you can still loop through the old table and update the number table as I described above.

edited Aug 20, 2012 at 4:27

answered Aug 20, 2012 at 4:21

Jared Drake

9645 silver badges12 bronze badges

Comments

Nir Alfasi · Accepted Answer · 2012-08-20 07:18:22Z

0

You can build a procedure that'll do it for you:

CREATE PROCEDURE insert_news_items()
BEGIN
DECLARE news_items_cur CURSOR FOR
    SELECT title, hits
    FROM blog_posts
    WHERE title LIKE '%slim charger%' AND category = 2;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;


OPEN news_items_cur;
LOOP

    IF done THEN
      LEAVE read_loop;
    END IF;

    FETCH  news_items_cur
    INTO   title, hits;

    INSERT INTO `news_items` (`title`, `hits`) VALUES (title, hits);

END LOOP;
CLOSE news_items_cur;
END;

answered Aug 20, 2012 at 7:18

Nir Alfasi

53.6k11 gold badges94 silver badges138 bronze badges

Collectives™ on Stack Overflow

Update multiple mysql rows with 1 query?

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related