追加新数据并通过插入另一个数据更新表中的现有数据

发布于 2025-01-17 06:34:11 字数 1212 浏览 5 评论 0原文

在 SQL 中，我有一个包含 3 列的表，该表每天都会刷新。我想将“表a”数据附加到“表b”。我需要确保按日期没有重复的行，而且销售计数会增加，销售计数会更新，而不是放入新行。

表 a

日期	id	sale_count
12/20/21	1	7
12/20/21	2	3
12/21/21	2	2

表 b 预更新

日期	id	sale_count
12/20/21	1	5
12/20/21	2	3

表 b发布更新

日期	ID	sale_count
12/20/21	1	7
12/20/21	2	3
12/21/21	2	2

到目前为止我的代码仍然允许重复行

INSERT INTO tableB
SELECT
  a.[date]
  ,a.id
  ,a.sale_count
FROM tableA a
Left outer join tableB b
on a.[date] = b.[date] 
and a.id = b.id
and a.sale_count = b.sale_count
where b.[date] IS NULL
AND b.sale_count is NULL

原文

In SQL, I have a table with 3 columns that is refreshed through the day. I want to append "table a" data to "table b". I need to ensure the there are no duplicate rows by date, but also that is the sale_count in creases that the sale count is updated and not put in a a new row.

table a

date	id	sale_count
12/20/21	1	7
12/20/21	2	3
12/21/21	2	2

table b pre-update

date	id	sale_count
12/20/21	1	5
12/20/21	2	3

table b post update

date	id	sale_count
12/20/21	1	7
12/20/21	2	3
12/21/21	2	2

code I have so far that is still allowing duplicate rows

INSERT INTO tableB
SELECT
  a.[date]
  ,a.id
  ,a.sale_count
FROM tableA a
Left outer join tableB b
on a.[date] = b.[date] 
and a.id = b.id
and a.sale_count = b.sale_count
where b.[date] IS NULL
AND b.sale_count is NULL

分享到QQ

分享到微博