更新 SQL 中的唯一行

发布于 2024-12-02 08:45:52 字数 417 浏览 1 评论 0原文

我有一个表，

id | col1 | col3| col4
1  | x    |  r  |
2  | y    |  m  |
3  | z    |  p  |
4  | x    |  r  |

我必须更新该表的所有唯一行即

  id | col1 | col3| col4
  1  | x    |  r  |  1
  2  | y    |  m  |  1
  3  | z    |  p  |  1
  4  | x    |  r  |  0

我可以通过获取唯一的行

  select distinct col1,col2 from table

。但是我如何识别这些行以便更新它们。请帮忙。

原文

I have a table

id | col1 | col3| col4
1  | x    |  r  |
2  | y    |  m  |
3  | z    |  p  |
4  | x    |  r  |

i have to update all unique rows of this table
i.e

  id | col1 | col3| col4
  1  | x    |  r  |  1
  2  | y    |  m  |  1
  3  | z    |  p  |  1
  4  | x    |  r  |  0

i can fetch unique rows by

  select distinct col1,col2 from table

.But how can i identify these rows in order to update them.Please help.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

歌入人心 2024-12-09 08:45:52

您可以使用分组依据来选择唯一的结果：

SELECT MIN(ID) AS ID FROM TABLE GROUP BY COL1, COL3;

  id | col1 | col3
  1  | x    |  r  
  2  | y    |  m  
  3  | z    |  p

然后

UPDATE TABLE SET col4 = 1 WHERE ID IN (SELECT MIN(ID) FROM TABLE GROUP BY COL1, COL3);

限制是id列应该是唯一的。

You can use the group by to pick unique result:

SELECT MIN(ID) AS ID FROM TABLE GROUP BY COL1, COL3;

  id | col1 | col3
  1  | x    |  r  
  2  | y    |  m  
  3  | z    |  p

Then

UPDATE TABLE SET col4 = 1 WHERE ID IN (SELECT MIN(ID) FROM TABLE GROUP BY COL1, COL3);

Restriction is that the id column should be unique.

回复收藏 0 原文

唔猫 2024-12-09 08:45:52

如果表足够小，您可以执行以下操作

步骤 1：将所有内容更新为 1

Update Table Set Col4 = 1

步骤 2：将所有重复更新为 0 (OTTOMH)

Update Table
Set Col4 = 0
From 
(
    Select Col1, Min (Id) FirstId
    From Table
    Group By Col1
    Having Count (*) > 1
) Duplicates
Where Table.Col1 = Duplicates.Col1
And Table.Id <> Duplicates.FirstId

If it is a small enough table, here is what you can do

Step 1: Update everything to 1

Update Table Set Col4 = 1

Step 2: Update all dups to 0 (OTTOMH)

Update Table
Set Col4 = 0
From 
(
    Select Col1, Min (Id) FirstId
    From Table
    Group By Col1
    Having Count (*) > 1
) Duplicates
Where Table.Col1 = Duplicates.Col1
And Table.Id <> Duplicates.FirstId

回复收藏 0 原文

入怼 2024-12-09 08:45:52

您还可以尝试：

UPDATE test
   SET col4 = 1
 WHERE id IN
     (
       SELECT t1.id
         FROM table_name t1
         LEFT JOIN table_name t2
           ON t2.id < t1.id
          AND t2.col1 = t1.col1
          AND t2.col3 = t1.col3
        WHERE t2.id IS NULL
     )

You can also try:

UPDATE test
   SET col4 = 1
 WHERE id IN
     (
       SELECT t1.id
         FROM table_name t1
         LEFT JOIN table_name t2
           ON t2.id < t1.id
          AND t2.col1 = t1.col1
          AND t2.col3 = t1.col3
        WHERE t2.id IS NULL
     )

回复收藏 0 原文

独行侠 2024-12-09 08:45:52

另一个稍微复杂的选项，一次设置 0 和 1 值：

update my_table mt
set col4 = (
    select case when rn = 1 then 1 else 0 end
    from (
        select id,
            row_number() over (partition by col1, col3 order by id) as rn
        from my_table) tt
    where tt.id = mt.id);

4 rows updated.

select * from my_table order by id;

        ID COL1 COL3       COL4
---------- ---- ---- ----------
         1 x    r             1
         2 y    m             1
         3 z    p             1
         4 x    r             0

这只是使用 row_number() 来决定哪一个唯一的组合是第一个，任意使用最低的id，将其值指定为1，其他所有值都为零。

One more slightly convoluted option, to set both 0 and 1 values in one hit:

update my_table mt
set col4 = (
    select case when rn = 1 then 1 else 0 end
    from (
        select id,
            row_number() over (partition by col1, col3 order by id) as rn
        from my_table) tt
    where tt.id = mt.id);

4 rows updated.

select * from my_table order by id;

        ID COL1 COL3       COL4
---------- ---- ---- ----------
         1 x    r             1
         2 y    m             1
         3 z    p             1
         4 x    r             0

This is just using row_number() to decide which of the unique combinations is first, arbitrarily using the lowest id, assigning that the value of one, and everything else zero.

回复收藏 0 原文

~没有更多了~