I want sql query when one column has same value-CodePudding

I want to run a query in which all the column number has same value and copy the address value in the other other rows from the row in which value of type is 0.

For example in the posted image, where the number column has value '1', copy the address to other row of from type = 0. That means all three rows where values of number column is 1, the value will of the address column will be ABC, ABC ,ABC and same in the number column where values are 2 , the address will be CSA,CSA.

CodePudding user response：

Something like this (take value for number with type = 0 and join it to a main table):

    WITH
    t AS (SELECT _number,
                 MAX(_address) AS _address -- here can be more than one row with type = 0 ?
              FROM test
              WHERE _type = 0
              GROUP BY _number)
SELECT test._id,
       test._number,
       test._type,
       t._address
    FROM test
    LEFT JOIN t
            ON test._number = t._number;

Here's dbfiddle example

upd. for update you can use this one:

UPDATE test
    INNER JOIN (SELECT
    _number,
    max(_address) AS _address -- here can be more than one row with type = 0 ?
    FROM test
    WHERE _type = 0
    GROUP BY _number) t
ON t._number = test._number
SET test._address = t._address;

updated dbfiddle here

CodePudding user response：

Another solution for MySQL 8 is as follows:

update test t1
inner join ( select `number`,`type`,`address`
             from ( select *,row_number() over(partition by `number` order by `type` desc ) row_num
                     from test
                   ) as tbl
             where row_num >1 and `type`=0 
            ) as tbl2 on t1.`number`=tbl2.`number`
set t1.address=tbl2.address;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=e07b26d47cfe793c720d5493c55b8866

First you need to find duplicate numbers using:

select `number`,`type`,`address`
from ( select *,row_number() over(partition by `number` order by `type` desc ) row_num
       from test
     ) as tbl
where row_num >1 and `type`=0 ;


order by `type` desc

is important because I applied

where row_num >1 and `type`=0

to get only the duplicate numbers, which the have type=0