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?

• user212533: Can you provide more detail around the expect output/results?
• Andriy M: Why does Site Name match Boq Item Name for 1.1.1 and 1.1.2? Why is it not a null or an empty string instead? What's the rule there?
• Raida Adn: @AndriyM The rule is that level indexes which have product no empty and level index not empty are the parent level indexes. Under each parent level index, child level indexes are present. I need to fill the site name of each child to the parent's boqitemname. Parent's site name hasn't to be touched.
• Raida Adn: @bbaird I updated the question with the expected output.
• Andriy M: @RaidaAdn: Sorry, I'm still confused. I understand why 1.1.1.1's Site Name should be Clamp, because that's Boq Item Name of its parent, item 1.1.1. But why should 1.1.1's Site Name be Clamp as well? It doesn't have a parent, and yet you showed that its Site Name is also filled, and in that case filled with its own Boq Item Name. Why?

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.