Postgresql：如何为除一行之外的字段字符串末尾的所有重复值更新一个字段

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

http://sqlfiddle.com/#!9/b98ea/1(示例表)

I have a table with the following fields:

我有一个包含以下字段的表:

transfer_id
src_path
DH_USER_ID
email
status_state
ip_address

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

src_path字段包含几个重复的文件名值,但在字符串的开头包含不同的文件夹名称。

Example:

191915/NequeVestibulumEget.mp3
/191918/NequeVestibulumEget.mp3
191920/NequeVestibulumEget.mp3

I am trying to do the following:

我正在尝试执行以下操作:

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

对于(src_path)字段中除1之外的所有重复文件名,将status_state字段设置为“cancelled”。

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

我希望结果看起来像这样: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,我需要帮助。

2 个解决方案

#1

SQL Fiddle Demo

SQL小提琴演示

fix_os_name: Fix the windows path string to unix format.

fix_os_name:将Windows路径字符串修复为unix格式。

file_name: Split the path using /, and use char_length to bring last split.

file_name:使用/拆分路径,并使用char_length进行最后一次拆分。

drank: Create a seq for each filename. So unique filename only have 1, but dup also have 2,3 ...

喝酒:为每个文件名创建一个seq。所以唯一的文件名只有1,但是dup也有2,3 ...

UPDATE: check if that row have rn > 1 mean is a dup.

更新:检查该行是否有rn> 1 mean是一个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

ADD:一行是触摸

#2

Use the regexp_matches function to separate the file name from the directory. From there you can use distinct() to build a table with unique values for the filename.

使用regexp_matches函数将文件名与目录分开。从那里你可以使用distinct()来构建一个具有文件名唯一值的表。

select
regexp_matches(src_path, '[a-zA-Z.0-9]*$') , *
from priority_transfer
;

#1