用1个查询更新多个mysql行？

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,

我正在将客户端数据库移植到具有不同帖子标题和行ID的新客户端数据库，但他希望保留旧网站的点击量，

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

他在新数据库中有超过500篇文章，更新一篇不是这个查询的问题

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

但是如何通过1个查询为所有500篇文章做这个呢？我已经有来自旧数据库的导出查询和帖子标题和点击，所以我们可以更容易地找到新的

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

两个表中唯一的引用是标题中的产品名称字，其他一切都不同，id，完整标题......

4 个解决方案

#1

Make a tmp table for old data in old_posts

在old_posts中为旧数据创建一个tmp表

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

#2

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.

你如何桥接它取决于你，但那是基本的伪代码/ 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.

用于从一个DB中进行选择并放入另一个DB的API因DBMS而异，因此您需要一个通用的中间格式。基本上从第一个DB获取记录，将其粘贴到您选择的编程语言中的结构中，并从使用这些结构值的插入开始，使用API用于其他DBMS。

#3

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.

我不是100％确定你可以一次更新多个记录，但我认为你想要做的是将循环与更新查询结合使用。

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.

保存你的最后一个可能的想法是id可能不同，但它们可能仍然具有相同的顺序。如果是这种情况，您仍然可以遍历旧表并更新数字表，如上所述。

#4

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;

#1