v*******s
发帖数: 450 | 1 3万个产品目录有重复的，这个个QUERY下架重复的老产品， 转了15分钟了还没完
update zen_products set products_status=0 where products_id not in( select
max(products_id) products_id from zen_products_description group by products
_name) | a9
发帖数: 21638 | 2 索引？
products
【在 v*******s 的大作中提到】
: 3万个产品目录有重复的，这个个QUERY下架重复的老产品， 转了15分钟了还没完
: update zen_products set products_status=0 where products_id not in( select
: max(products_id) products_id from zen_products_description group by products
: _name)
| i*****w
发帖数: 75 | 3 Use Partition and if you have the ID indexed, it should be very fast. Try
the example here and you can change it according to your table structure.
declare @tbl Table(id INT identity, name varchar(30), isActive bit)
insert into @tbl values('Product One', 1)
insert into @tbl values('Product One', 1)
insert into @tbl values('Product One', 1)
insert into @tbl values('Product Two', 1)
insert into @tbl values('Product Two', 1)
insert into @tbl values('Product Three', 1)
insert into @tbl values('Product Three', 1)
insert into @tbl values('Product Three', 1)
insert into @tbl values('Product Four', 1)
--select * from @tbl
Update a
set a.IsActive = 0
FROM @tbl a
INNER JOIN
(
SELECT ID, RANK() over (partition by name order by id desc) rk from @tbl
) b
ON a.ID = B.ID
WHERE b.rk > 1
select * from @tbl
products
【在 v*******s 的大作中提到】
: 3万个产品目录有重复的，这个个QUERY下架重复的老产品， 转了15分钟了还没完
: update zen_products set products_status=0 where products_id not in( select
: max(products_id) products_id from zen_products_description group by products
: _name)
| v*******s
发帖数: 450 | 4 PK: products_id
any idea?
【在 a9 的大作中提到】
: 索引？
:
: products
| g***l
发帖数: 18555 | 5 zen_products 要有PK PRODUCT_ID， 把select
max(products_id)
into #TEMP_TABLE
products_id from zen_products_description group by products
_name
然后再UPDATE | i*****w
发帖数: 75 | 6 UPDATE a
SET a.products_status = 0
FROM zen_products a
INNER JOIN
(
SELECT products_id, RANK() over (partition by products_name order by
products_id desc) rk from zen_products_description
) b
ON a.products_id = b.products_id
WHERE b.rk > 1 |
|
