时间戳

首页 > 解决方案 > 如何使用单个 SQL 命令插入数据和删除重复行

sql - 如何使用单个 SQL 命令插入数据和删除重复行

问题描述

如果表中存在任何重复条目，那么将数据插入表同时删除的最佳方法是什么。有一种方法可以将重复的数据标识符存储到临时表中，然后将其删除。但这不是一种有效的方法。任何更好的想法将不胜感激。

我的桌子

CREATE TABLE account(  
  user_id serial PRIMARY KEY,  
  username VARCHAR(50) UNIQUE NOT NULL,  
  password VARCHAR (50) NOT NULL,  
  email VARCHAR(355) UNIQUE NOT NULL,  
  created_on TIMESTAMP NOT NULL,  
  last_login TIMESTAMP 
);

标签： sqlpostgresql

解决方案

“立即”的答案是简单地运行 DELETE 语句，然后在单个事务中运行 INSERT 语句。

假设您想要避免重复的用户名，那么您可以执行以下操作：

begin transaction
  delete from account
  where username = 'arthur';

  insert into account(username, password, email, created_on)
  values ('arthur', '****', 'arthur@h2g2.com', current_timestamp);
commit;

您可以将其组合成一个语句，但这并没有太大的区别：

with new_values (username, password, email, created_on) as (
  values values ('arthur', '****', 'arthur@h2g2.com', current_timestamp);
), deleted as (
  delete from the_table 
  where username = (select username from new_values)
) 
insert into account
select *
from new_values;

这里唯一的优点是您不需要重复两次值。

但是，如果account被其他表引用（即外键“指向” the_table），那么这将不起作用，因为如果该行仍然被引用，则 DELETE 将失败。

更好的解决方案是使用INSERT ON CONFLICT并使用新数据更新现有行：

insert into account(username, password, email, created_on)
values ('arthur', '****', 'arthur@h2g2.com', current_timestamp)
on conflict (username) 
do update 
  set password = excluded.password, 
      email = excluded.email;

但是，如果电子邮件已经存在，这仍然会引发错误，但不幸的是，on conflict do update您只能指定一个唯一约束。

为了处理两个不同的唯一约束，事情变得有点复杂：

with new_values (username, password, email, created_on) as (
  values 
    ('arthur', '***', 'arthur@h2g2.com', current_timestamp)
), inserted as (
  insert into account(username, password, email, created_on)
  select * from new_values
  on conflict do nothing
  returning id
)
update account
  set password = nv.password
from new_values nv  
where (account.username = nv.username or account.email = nv.email)
  and not exists (select * from inserted);

首先进行插入尝试。如果违反了任何唯一约束，则简单地忽略插入（on conflict do nothing）。

只有在上一步没有插入任何行时，才会执行最后的 UPDATE 语句。这是通过and not exists (select * from inserted.

由于用户名或电子邮件可能导致违反约束，因此更新对这两列使用 or 条件来更新现有行。如果你愿意，你也可以在那里更新更多的列。

推荐阅读