Postgresql:如何根据字段字符串的末尾(一行除外)为所有重复值更新一个字段 [英] Postgresql : How to update one field for all duplicate values based at the end of the string of a field except one row

问题描述

http://sqlfiddle.com/#!9/b98ea/1 (Sample Table)

I have a table with the following fields:

1. transfer_id
2. src_path
3. DH_USER_ID
4. email
5. status_state
6. ip_address

src_path field contains a couple of duplicates filename values but a different folder name at the beginning of the string.

Example:

1. 191915/NequeVestibulumEget.mp3
2. /191918/NequeVestibulumEget.mp3
3. 191920/NequeVestibulumEget.mp3

I am trying to do the following:

1. Set status_state field to 'canceled' for all the duplicate filenames within (src_path) field except for one.

I want the results to look like this: http://sqlfiddle.com/#!9/5e65f/2

*I apologize in advance for being a complete noob, but I am taking SQL at college and I need help.

解决方案

SQL Fiddle Demo

• fix_os_name: Fix the windows path string to unix format.
• file_name: Split the path using /, and use char_length to bring last split.
• drank: Create a seq for each filename. So unique filename only have 1, but dup also have 2,3 ...
• UPDATE: check if that row have rn > 1 mean is a dup.

.

Take note the color highlight is wrong, but code runs ok.

with fix_os_name as (
    SELECT transfer_id, replace(src_path,'\','/') src_path, 
    DH_USER_ID, email, status_state, ip_address
    FROM priority_transfer p
),  
file_name as (
    SELECT 
       fon.*,
       split_part(src_path,
                  '/',
                  char_length(src_path) - char_length(replace(src_path,'/','')) + 1
                 ) sfile
    FROM fix_os_name fon
), 
drank as (
    SELECT 
        f.*,
        row_number() over (partition by sfile order by sfile) rn
    from file_name f
)
UPDATE priority_transfer p
SET status_state = 'canceled'
WHERE EXISTS ( SELECT *
               FROM drank d
               WHERE d.transfer_id = p.transfer_id
               AND  d.rn > 1);

ADD: One row is untouch

这篇关于Postgresql:如何根据字段字符串的末尾(一行除外)为所有重复值更新一个字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！