How to update different id as same id using same name in sql database

user_8775896 0
Viewed 1037

I have to eliminate different id and update the ids with the first id using the same product name column in SQL for example:

Actual table:

id     product_name
-------------------
p1     apple
p2     apple
p3     grape
p4     grape
p5     grape

The updated table should be like: 

id     product_name
-------------------
p1     apple
p1     apple
p3     grape
p3     grape
p3     grape
amazon-redshift sql
7 Answers

You could try and join a derived table that computes the target id, as follows:

update t
set t.id = x.id
from mytable t
inner join (
    select product_name, min(id) id from mytable group by product_name
) x on x.product_name = t.product_name
user_106767160

You can try using common table expression. common table expression in redshift

;WITH CTE_productName AS
(
SELECT product_name, min(id)
FROM Product
)
UPDATE p
SET p.id = c.id
FROM Product AS p
JOIN CTE_ProductName AS c
ON p.product_name = c.product_name
user_6349350

Can be done using UPDATE with correlated sub query

UPDATE table1 AS a SET id = (SELECT MIN(id) FROM table1 AS b WHERE
b.product_name = a.product_name );

See here. Tested with Postgresql 10 not amazon redshift

user_13987860

Use group by with min to update with the below corelated subquery 

    Update table  t set id=(Select min(id) 
        from 
     table t where 
      product_name=t.product_name   
     group by product_name  )
user_104974830

You can use case and subquery

hope this help :)

UPDATE table_name SET id= ( case when product_name= 'apple' then 'p1' when product_name= 'grape' then 'p3' else id END );
user_28055070 
UPDATE table_name SET id= (
  case when product_name= 'apple' then 'p1' 
  when product_name= 'grape' then 'p3' 
  else id END );
Admin1

In Redshift, I would phrase this as:

update actual t
    set id = tt.min_id
    from (select product_name, min(id) as min_id
          from actual t
          group by product_name
         ) tt
     where tt.product_name = t.product_name and
           tt.min_id <> t.id;

I assume this is a simplification of your actual problem. Having a table with exact duplicates is usually not helpful.

user_11440350
Related