在将记录从一个表复制到另一个表时更改值

I have the following code to copy data from one table to another:

我有以下代码将数据从一个表复制到另一个表:

INSERT INTO MyDb.Books (CategoryId, Author, Title)
SELECT 
  CategoryId, Author, Title
FROM MyDbBackup.Books

I need to apply the following transformation when copying CategoryId values:

我需要在复制CategoryId值时应用以下转换:

+---------------------+-----------------+
|     Old CategoryId  |  New CategoryId |
+---------------------+-----------------+
|                  1  |               2 |
|                  2  |               1 |
|                  3  |               3 |
|                  4  |               4 |
|                  5  |               8 |
|                 14  |               6 |
|                 15  |               7 |
|                 18  |               5 |
|                 22  |               9 |
+---------------------+-----------------+

How can I do this?

我该怎么做呢?

4 个解决方案

#1

You could use case when

你可以用case when

INSERT INTO MyDb.Books (CategoryId, Author, Title)
SELECT 
  case when CategoryId = 1 then 2
       when CategoryId = 2 then 1
       when CategoryId = 5 then 8
       when CategoryId = 14  then 6
       when CategoryId = 15  then 7
       when CategoryId = 18  then 5
       when CategoryId = 22  then 9
       else cateogoryId end, Author, Title
FROM MyDbBackup.Books

or a less verbose way

或者用更简洁的方式

INSERT INTO MyDb.Books (CategoryId, Author, Title)
SELECT 
  case CategoryId 
       when  1 then 2
       when 2 then 1
       when 5 then 8
       when 14  then 6
       when 15  then 7
       when 18  then 5
       when 22  then 9
       else cateogoryId end, Author, Title
FROM MyDbBackup.Books

#2

As said in the comments by Panagiotis Kanavos, you'll need to use a CASE expression:

正如Panagiotis Kanavos的评论所说，您需要使用一个CASE表达式:

CASE CategoryID WHEN 1 THEN 2
                WHEN 2 THEN 1
                WHEN 5 THEN 8
                WHEN 14 THEN 6
                WHEN 15 THEN 7
                WHEN 18 THEN 5
                WHEN 22 THEN 9
                ELSE CategoryID END

#3

One method is to use a lookup table a join:

一种方法是使用查找表连接:

select v.newid, b.author, b.title
from MyDbBackup.Books b join
     (values (1, 2), (2, 1), (3, 3), (4, 4), (5, 8), (14, 6), (15, 7), (18, 5), (22, 9)
     ) v(oldid, newid)
     on b.CategoryId = v.oldid;

An alternative is to use a case expression. However, using the join ensures that only the set of books with the old ids is in the result set. So, it does both the lookup and filtering.

另一种方法是使用case表达式。但是，使用join可以确保结果集中只有具有旧id的图书集，因此，它同时执行查找和过滤。

If you don't want the filtering, you can use a left join instead of an inner join.

如果不想要过滤，可以使用左连接而不是内连接。

#4

Since there is no formula, then the conversion must be done one by one:

由于没有公式，那么转换必须一个一个地进行:

INSERT INTO MyDb.Books (CategoryId, Author, Title)
SELECT CASE CategoryID WHEN 1 THEN 2
                       WHEN 2 THEN 1
                       WHEN 5 THEN 8
                       WHEN 14 THEN 6
                       WHEN 15 THEN 7
                       WHEN 18 THEN 5
                       WHEN 22 THEN 9
                      ELSE CategoryId ID END AS CategoryId
       , Author, Title
FROM MyDbBackup.Books

#1