php - 更新重复记录保持1条记录不变的SQL语句

Question

我需要帮助来获得针对这种情况的解决方案。我有一个包含记录的表，有一个字段 sku，在这个记录中我有多次出现的 sku。表结构是这样的rid|id|sku|name

如果任何 sku 在表中多次可用，则记录如下所示

rid  id  sku     name
---  --  ------  --------------

1    3   rs-123  test product

2    3   rs-123  test product

3    4   rs-125  test product 2

4    4   rs-125  test product 2

5    4   rs-125  test product 2

6    6   rs-126  test product 3

我需要的是用重复记录更新表，保持第一个记录不变 (N-1)。因此，例如我需要运行只更新重复记录的 SQL 语句，因此如果更新了第一条记录，它将如下所示

rid  id  sku     name
---  --  ------  --------------
1    3   rs-123  test product
2    3   updated  updated 

我试图用这个 SQL 语句实现一些东西，但它不起作用

WITH duplicates AS (
  SELECT
ROW_NUMBER() OVER (PARTITION BY id ORDER BY rid) AS duplicate_id,
*
FROM
test
)

UPDATE
  duplicates
SET
  sku = updated
WHERE
  duplicate_id > 1

如有任何建议，我们将不胜感激。

Answer 1

也许您可以尝试使用变量:

SQLFIDDLE DEMO

set @sku:='';

select a.rid, a.id, 
@sku:= (case when @sku<>a.sku
        then a.sku else 'updated'
        end) as skus, @sku:=a.sku, a.name
from skus a
;

| RID | ID |    SKUS | @SKU:=A.SKU |           NAME |
-----------------------------------------------------
|   1 |  3 |  rs-123 |      rs-123 |   test product |
|   2 |  3 | updated |      rs-123 |   test product |
|   3 |  4 |  rs-125 |      rs-125 | test product 2 |
|   4 |  4 | updated |      rs-125 | test product 2 |
|   5 |  4 | updated |      rs-125 | test product 2 |
|   6 |  6 |  rs-126 |      rs-126 | test product 3 |

用于更新:

SQLFIDDLE DEMO for UPDATING

set @sku:='';

UPDATE
  skus a
  join 
  (select a.rid, a.id, 
@sku:= (case when @sku<>a.sku
        then a.sku else 'updated'
        end) as skus, @sku:=a.sku, a.name
from skus a) b
  on a.rid = b.rid
SET
  a.sku = 'up_again'
WHERE
  b.skus = 'updated'
;

| RID | ID |      SKU |           NAME |
----------------------------------------
|   1 |  3 |   rs-123 |   test product |
|   2 |  3 | up_again |   test product |
|   3 |  4 |   rs-125 | test product 2 |
|   4 |  4 | up_again | test product 2 |
|   5 |  4 | up_again | test product 2 |
|   6 |  6 |   rs-126 | test product 3 |

这是另一个使用 Joins 的例子:

http://sqlfiddle.com/#!2/ddf47/2

update skus a
join skus b
on a.rid = b.rid +1
set a.sku = 'updated'
where a.sku = b.sku
;


| RID | ID |     SKU |           NAME |
---------------------------------------
|   1 |  3 |  rs-123 |   test product |
|   2 |  3 | updated |   test product |
|   3 |  4 |  rs-125 | test product 2 |
|   4 |  4 | updated | test product 2 |
|   5 |  4 | updated | test product 2 |
|   6 |  6 |  rs-126 | test product 3 |

同时更新 sku 和名称:

http://sqlfiddle.com/#!2/97f4f/1

update skus a
join skus b
on a.rid = b.rid +1
set a.sku = 'updated', a.name = 'new_name'
where a.sku = b.sku
;

| RID | ID |     SKU |           NAME |
---------------------------------------
|   1 |  3 |  rs-123 |   test product |
|   2 |  3 | updated |       new_name |
|   3 |  4 |  rs-125 | test product 2 |
|   4 |  4 | updated |       new_name |
|   5 |  4 | updated |       new_name |
|   6 |  6 |  rs-126 | test product 3 |

---

在 SQL SERVER 中使用 ROW_NUMBER() 查看 OP 的问题

http://sqlfiddle.com/#!3/355c4/2

update a 
set a.sku = 'updated', a.name = 'new_name'
from skus a
join (Select rid, id, row_number() over (
  partition by id order by rid asc) rank
from skus) b
on a.rid = b.rid
where b.rank > 1
;

| RID | ID |     SKU |           NAME |
---------------------------------------
|   1 |  3 |  rs-123 |   test product |
|   2 |  3 | updated |       new_name |
|   3 |  4 |  rs-125 | test product 2 |
|   4 |  4 | updated |       new_name |
|   5 |  4 | updated |       new_name |
|   6 |  6 |  rs-126 | test product 3 |