CODEWITHSUNDEEP
mysql
Benn
Benn

Reputation: 5013

Update multiple mysql rows with 1 query?

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 ...

Upvotes: 1

Views: 181

Answers (4)

Nir Alfasi
Nir Alfasi

Reputation: 53525

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;

Upvotes: 0

fanlix
fanlix

Reputation: 1378

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;

Upvotes: 3

Jared Drake
Jared Drake

Reputation: 1002

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.

Upvotes: 0

awiebe
awiebe

Reputation: 3836

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.

Upvotes: 0

Related Questions