Geek Answers Handbook

Effective Approach for Bulk Updating a Hierarchical Table

I have a database table representing a hierarchy of files and directories with the following structure (simplified):

ItemId int
Path text
Type int (0 for files, 1 for directories)
ParentId int
Backuptime datetime

Currently, the column is BackupTimeused only for files; for directories, it is null.

Now I need to fill this column for directories too: it should be the minimum of BackupTimeall descendants (files and directories).

This (naive and inefficient) request illustrates what I want to do:

update Items i
set BackupTime = (select min(BackupTime)
                  from Items d
                  where d.Path like i.Path || '%'
                  and d.Type = 0)
where i.Type = 1

, . ( 100 . )

, min(BackupTime) :

update Items i
set BackupTime = (select min(BackupTime)
                  from Items d
                  where d.ParentId = i.ItemId)
where i.Type = 1

, , , , . , , . SQLite, .

, ?

UPDATE, , ,

+5
1

, . . ( sqlite, , , SQL-92 , , .)

1: , . , , , -NULL BackupTime. (, BackupTime, , , BackupDate . , .)

2. , :

  update Items i set
    BackupTime = (
      select min(BackupTime)
      from Items d
      where d.ParentId = i.ItemId
    )
  where i.Type = 1
  and i.BackupTime is null
  and not exists (
    select *
    from Items d
    where d.ParentId = i.ItemId
    and d.Type = 1
    and d.BackupTime is null
  )

, BackupTime , , : BackupTime null, , BackupTime .

, , , BackupTime , , . BackupTime , , -.

, , BackupTime ((select...), current_timestamp).

+1

More articles:


All Articles