Updating a column value using a sub select join to the same table

Question

The table BillOfQuantitiy has level index and boqitemname, sitename and many more. The data visualization is as follows

|Level Index    | Boq Item Name | Site Name| 
|---------------|---------------|----------|
|1.1.1          | Clamp         |          |
|1.1.1.1        | Hand Clamp    |          |
|1.1.1.2        | Door Clamp    |          |
|1.1.2          | Switches      |          |
|1.1.2.1        | Two way switch|          |

and so on

The expected output is

|Level Index    | Boq Item Name | Site Name| 
|---------------|---------------|----------|
|1.1.1          | Clamp         |  Clamp   |
|1.1.1.1        | Hand Clamp    |  Clamp   |
|1.1.1.2        | Door Clamp    |  Clamp   |
|1.1.2          | Switches      | Switches |
|1.1.2.1        | Two way switch| Switches |

I need to update the sitename to the parent level index's BoqItemnamei.e for 1.1.1.1 the parent level index is 1.1.1 whose BoqItemname is Clamp.

The same goes for 1.1.1.2. For 1.1.2.1 the sitename would be Switches

The way I decided to build the query is

1. select all the parent Items a

2. select all the child items b

3. Inner join these above two based on the a.levelindex = substring(b.levelindex,1,length(a.levelindex))

4. selecting the levelindex, lblindex, boqitemname, substring(b.levelindex,1,length(a.levelindex) as benev from the above inner join

5. Updating the sitename where levelindex=benev and lblindex ==boqtable.levelindex

   update billofquantity btt
   set sitename=fin.boqitemname
   from(
        select levelindex,boqitemname, lblindex, substring(lblindex,1,lenght(levelindex)) as benev 
         from(
               select * from (
                               select levelindex, boqitemname from billofquantity where productno='' and levelindex!='' and lenght(levelindex)>2) a 
                                join(
                                select distinct levelindex lblindex from billofquantity where boqitemid!='' and levelindex!='' and length(levelindex)>2 ) b
                                 on a.levelindex = substring(b.lblindex,1,length(a.levelindex)
             ) as foo 
                 where foo.levelindex=substring(foo.lblindex,1,length(foo.levelindex))
   ) as fin where fin.levelindex = fin.benev
         and  fin.lblindex = btt.levelindex
   

When I do the selection which is inside of the update, it shows that levelindex, boqitemname, lblindex and benev where levelindex and benev will be same. Hence I want to filter the update on those same valued columns and also on the lblindex equal to the updating table levelindex.

It shows that the update happend but the sitename remains null. The query doesn't update may be due to selection and updation happening on the same table?

Answer 1

You could use a correlated subquery like this:

UPDATE
  BillOfQuantity AS tgt
SET
  SiteName =
  (
    SELECT
      src.BoqItemName
    FROM
      BillOfQuantity AS src
    WHERE
      tgt.LevelIndex LIKE src.LevelIndex || '%'
      AND tgt.LevelIndex <> src.LevelIndex
    ORDER BY
      src.LevelIndex DESC
    LIMIT
      1
  )
;

Note that a top-level item's SiteName will be set to null this way. If you want it to be set to the item's BoqItemName instead, you could wrap the subquery in COALESCE and specify tgt.BoqItemName as the substitution for the null:

UPDATE
  BillOfQuantity AS tgt
SET
  SiteName =
    COALESCE(
      (
        SELECT
          src.BoqItemName
        FROM
          BillOfQuantity AS src
        WHERE
          tgt.LevelIndex LIKE src.LevelIndex || '%'
          AND tgt.LevelIndex <> src.LevelIndex
        ORDER BY
          src.LevelIndex DESC
        LIMIT
          1
      )
    , tgt.BoqItemName
    )
;

You can try both options online using this live demo.