如果父行中的字段值相同

发布于 2025-01-23 09:26:23 字数 316 浏览 0 评论 0 原文

我在两个表中有数据。城市和地址。

现在，ID 2和3的城市

id - name - code
1  - abc  - 1
2  - xyz  - 2
3  - efg  - 2

地址

id - city_id - name
1  -   1     - wer
2  -   2     - sdf
3  -   3     - tyu

代码相同。想用ID 3删除城市，并在地址表中更改City_id 3至2

我相应的孩子表。我的两张桌子中有成千上万的行。

原文

I have data in two tables. City and Address.

City

id - name - code
1  - abc  - 1
2  - xyz  - 2
3  - efg  - 2

Address

id - city_id - name
1  -   1     - wer
2  -   2     - sdf
3  -   3     - tyu

Now code for cities with id 2 and 3 are the same. I want to delete the city with id 3 and change city_id 3 to 2 in the Address table so that the last two rows of Address table are associated with city id 2

In other words, I want to delete duplicate rows in the parent table and update the child table accordingly.
There are thousands of such rows in my 2 tables.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

冰之心 2025-01-30 09:26:23

如果您要多次执行此操作，那么我建议您创建一个存储过程，并在需要时调用它。

CREATE OR REPLACE PROCEDURE delete_dulicate_city()
LANGUAGE 'plpgsql'
AS $
BEGIN
    DROP TABLE IF EXISTS temp_city;

    CREATE TEMPORARY TABLE IF NOT EXISTS temp_city
    (
        id_to                      INT,
        id_from                    INT
    );
        
    INSERT INTO temp_city
    SELECT c1.id id_to, c2.id id_from 
    FROM
    (
        SELECT id, code, row_number() over(partition BY code ORDER BY id) rn
        FROM city
    ) c1 
    INNER JOIN city c2 
    ON  c1.rn = 1
    AND c1.code=c2.code 
    AND c2.id > c1.id;

    UPDATE address a
    SET city_id = tc.id_to
    FROM temp_city tc
    WHERE a.city_id=tc.id_from;

    DELETE FROM city 
    WHERE id IN (SELECT id_from FROM temp_city);
END;
$;

您只需执行呼叫delete_duplicate_city（）;

If you are going to do this multiple times then I would suggest you to create a Stored Procedure and call it whenever you need it.

CREATE OR REPLACE PROCEDURE delete_dulicate_city()
LANGUAGE 'plpgsql'
AS $
BEGIN
    DROP TABLE IF EXISTS temp_city;

    CREATE TEMPORARY TABLE IF NOT EXISTS temp_city
    (
        id_to                      INT,
        id_from                    INT
    );
        
    INSERT INTO temp_city
    SELECT c1.id id_to, c2.id id_from 
    FROM
    (
        SELECT id, code, row_number() over(partition BY code ORDER BY id) rn
        FROM city
    ) c1 
    INNER JOIN city c2 
    ON  c1.rn = 1
    AND c1.code=c2.code 
    AND c2.id > c1.id;

    UPDATE address a
    SET city_id = tc.id_to
    FROM temp_city tc
    WHERE a.city_id=tc.id_from;

    DELETE FROM city 
    WHERE id IN (SELECT id_from FROM temp_city);
END;
$;

You can call it whenever you need just by executing CALL delete_duplicate_city();

https://onecompiler.com/postgresql/3xzy48uq4

回复收藏 0 原文

情感失落者 2025-01-30 09:26:23

创建一个要处理的成对表（CC），然后更新地址并因此从城市中删除，因此

   select c1.id idto, c2.id idfrom into cc 
   from (
     select id,  code,  row_number() over(partition by code order by id) rn
     from city 
   ) c1 
   join city c2 on c1.rn = 1 and c1.code = c2.code and c2.id > c1.id;

update Address a
set city_id = cc.idto
from  cc
where a.city_id=cc.idfrom;

delete from city 
where id in (select idfrom from cc);

Create a table of pairs to be processed (cc), then update Address and delete from City accordingly

   select c1.id idto, c2.id idfrom into cc 
   from (
     select id,  code,  row_number() over(partition by code order by id) rn
     from city 
   ) c1 
   join city c2 on c1.rn = 1 and c1.code = c2.code and c2.id > c1.id;

update Address a
set city_id = cc.idto
from  cc
where a.city_id=cc.idfrom;

delete from city 
where id in (select idfrom from cc);

db<>fidle

回复收藏 0 原文

~没有更多了~