将 1 个任意行指定为每组的主要行

发布于 2024-11-03 12:59:09 字数 266 浏览 1 评论 0原文

我有一个包含三列的表，

foo_id | bar_id | is_primary

现在导入大量记录后，id 对都在其中，但 is_primary 到处都是 0，我需要为每个 foo_id 分配 1 任意行代码> as is_primary = 1？

foo_id | bar_id 是主唯一键，但是 foo_id 和 bar_id 都可以出现多次

原文

I have a table with three columns,

foo_id | bar_id | is_primary

Now after importing a huge amount of records the id pairs are all in but the is_primary is 0 everywhere, I need to assign 1 arbitrary row per foo_id as is_primary = 1?

foo_id | bar_id are the primary unique keys, however both foo_id and bar_id can appear multiple times

分享到QQ

分享到微博

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

发布评论

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

友谊不毕业 2024-11-10 12:59:09

假设所有 (foo_id, bar_id) 对都是唯一的，并且 is_primary 被限制为 0 或 1（即没有其他值或 NULL），您可以确定，例如，每个 foo_id 值的最小 bar_id 值，并将相应行的 is_primary 设置为 1：

UPDATE atable t
  INNER JOIN (
    SELECT
      foo_id,
      MIN(bar_id) AS bar_id
    FROM atable
    GROUP BY foo_id
    HAVING MAX(is_primary) = 0
  ) s ON t.foo_id = s.foo_id AND t.bar_id = s.bar_id
SET t.is_primary = 1

Assuming all the (foo_id, bar_id) pairs are unique and is_primary is restricted to have either 0 or 1 (i.e. no other values or NULLs), you could determine, for example, the minimum bar_id value for every foo_id value and set is_primary to 1 for the corresponding rows:

UPDATE atable t
  INNER JOIN (
    SELECT
      foo_id,
      MIN(bar_id) AS bar_id
    FROM atable
    GROUP BY foo_id
    HAVING MAX(is_primary) = 0
  ) s ON t.foo_id = s.foo_id AND t.bar_id = s.bar_id
SET t.is_primary = 1

回复收藏 0 原文

天赋异禀 2024-11-10 12:59:09

假设 (foo_id,bar_id) 是唯一的，您可以使用 notists 子句来过滤每个 foo_id 的 bar_id 的最高值：

update  YourTable yt1
set     is_primary = 1
where   not exists
        (
        select  *
        from    YourTable yt2
        where   yt2.foo_id = yt1.foo_id
                and yt2.bar_id > yt1.bar_id
        )

Assuming that (foo_id,bar_id) is unique, you could use a not exists clause to filter the highest value of bar_id per foo_id:

update  YourTable yt1
set     is_primary = 1
where   not exists
        (
        select  *
        from    YourTable yt2
        where   yt2.foo_id = yt1.foo_id
                and yt2.bar_id > yt1.bar_id
        )

回复收藏 0 原文

~没有更多了~