代码之家 › 专栏 › 技术社区 › Pedro Lopes

在更新选定内容的字段时插入选定内容

sql-server sql

Pedro Lopes · 技术社区 · 6 年前

我需要在我的数据库中重新插入数据,只有一个字段不同。有没有办法让我用一个查询就可以做到这一点。所以从更新的选择插入。

这是我需要的选择

我需要再次在我的数据库中插入此选择,但将季节代码从82更改为91。所以我的数据库中的数据是双倍的,其中有一行是不同的。有没有办法让我用一个查询来完成这个任务?

这是我目前的查询。但我不知道如何添加到这个更新。

  INSERT INTO [OrdForm].[dbo].[SeasonDepSexGroup]
SELECT * from [SeasonDepSexGroup]
  where DepartmentCode = 'UD' and KlasseCode = 'U'

我试图在网上搜索这个,但找不到这样的查询。

3 回复 | 直到 6 年前

Carra 6 年前

你在正确的轨道上。只需为季节代码选择所需的列+'91'。

INSERT INTO [OrdForm].[dbo].[SeasonDepSexGroup]
  (Code, Sex10Code, DepartmentCode, LastModified, SeasonCode, KlasseCode, CompanyCode, Kenmerk2)
SELECT Code, Sex10Code, DepartmentCode, LastModified, 91, KlasseCode, CompanyCode, Kenmerk2 
  FROM [SeasonDepSexGroup]
  WHERE DepartmentCode = 'UD' and KlasseCode = 'U'

D-Shih 6 年前

你需要在上面写清楚列名 select 你可以试着用 CASE WHEN 让你期待。

INSERT INTO [OrdForm].[dbo].[SeasonDepSexGroup]
      (Code,Sex10Code,LastModified,SeasoneCode ....)
SELECT t.Code,
       t.Sex10Code,
       t.DepartmentCode
       t.LastModified
       (CASE WHEN t.SeasoneCode = 82 then 91 else t.SeasoneCode end) SeasoneCode 
       ...
from [SeasonDepSexGroup] t
where DepartmentCode = 'UD' and KlasseCode = 'U'

Wei Lin 6 年前

@卡拉和@d-shih的回答很好

有人给你写信:-)

INSERT INTO [SeasonDepSexGroup]
  SELECT [Code], [Sexl Code], [DepartmentCode], [Last Modified], 92, [KlasseCode], [CompanyCode], [Kenmerk2] from [SeasonDepSexGroup]
  where DepartmentCode = 'UD' and KlasseCode = 'U'
  and SeasonCode = 82
  union all
  SELECT * from [SeasonDepSexGroup]
  where DepartmentCode = 'UD' and KlasseCode = 'U'
  and SeasonCode <> 82;

结果:

| Code | Sexl Code | DepartmentCode |            Last Modified | SeasonCode | KlasseCode | CompanyCode | Kenmerk2 |
|------|-----------|----------------|--------------------------|------------|------------|-------------|----------|
| TSHI |         2 |             UD |  2018-02-22T13:37:50.46Z |         82 |          U |         GED |   (null) |
| ACCE |         1 |             UD |  2018-02-22T13:37:50.46Z |         82 |          U |         GED |   (null) |
| ACWI |         1 |             UD | 2018-02-22T13:37:50.463Z |         82 |          U |         GED |   (null) |
| BERM |         1 |             UD | 2018-02-22T13:37:50.463Z |         82 |          U |         GED |   (null) |
| BODW |         1 |             UD | 2018-02-22T13:37:50.463Z |         82 |          U |         GED |   (null) |
| BROE |         1 |             UD | 2018-02-22T13:37:50.467Z |         82 |          U |         GED |   (null) |
|  DAS |         1 |             UD | 2018-02-22T13:37:50.467Z |         82 |          U |         GED |   (null) |
| DENI |         1 |             UD | 2018-02-22T13:37:50.467Z |         82 |          U |         GED |   (null) |
| GILE |         1 |             UD | 2018-02-22T13:37:50.467Z |         82 |          U |         GED |   (null) |
| HEML |         1 |             UD |  2018-02-22T13:37:50.47Z |         82 |          U |         GED |   (null) |
| LEDE |         1 |             UD |  2018-02-22T13:37:50.47Z |         82 |          U |         GED |   (null) |
| LING |         1 |             UD |  2018-02-22T13:37:50.47Z |         82 |          U |         GED |   (null) |
| MANT |         1 |             UD | 2018-02-22T13:37:50.473Z |         82 |          U |         GED |   (null) |
|  MTO |         1 |             UD | 2018-02-22T13:37:50.473Z |         82 |          U |         GED |   (null) |
| TSHI |         2 |             UD |  2018-02-22T13:37:50.46Z |         92 |          U |         GED |   (null) |
| ACCE |         1 |             UD |  2018-02-22T13:37:50.46Z |         92 |          U |         GED |   (null) |
| ACWI |         1 |             UD | 2018-02-22T13:37:50.463Z |         92 |          U |         GED |   (null) |
| BERM |         1 |             UD | 2018-02-22T13:37:50.463Z |         92 |          U |         GED |   (null) |
| BODW |         1 |             UD | 2018-02-22T13:37:50.463Z |         92 |          U |         GED |   (null) |
| BROE |         1 |             UD | 2018-02-22T13:37:50.467Z |         92 |          U |         GED |   (null) |
|  DAS |         1 |             UD | 2018-02-22T13:37:50.467Z |         92 |          U |         GED |   (null) |
| DENI |         1 |             UD | 2018-02-22T13:37:50.467Z |         92 |          U |         GED |   (null) |
| GILE |         1 |             UD | 2018-02-22T13:37:50.467Z |         92 |          U |         GED |   (null) |
| HEML |         1 |             UD |  2018-02-22T13:37:50.47Z |         92 |          U |         GED |   (null) |
| LEDE |         1 |             UD |  2018-02-22T13:37:50.47Z |         92 |          U |         GED |   (null) |
| LING |         1 |             UD |  2018-02-22T13:37:50.47Z |         92 |          U |         GED |   (null) |
| MANT |         1 |             UD | 2018-02-22T13:37:50.473Z |         92 |          U |         GED |   (null) |
|  MTO |         1 |             UD | 2018-02-22T13:37:50.473Z |         92 |          U |         GED |   (null) |

db<>fiddle Test Demo Link