0

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?

6
  • Can you provide more detail around the expect output/results?
    – bbaird
    Aug 9 '21 at 14:18
  • 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?
    – Andriy M
    Aug 9 '21 at 14:57
  • @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
    Aug 10 '21 at 6:00
  • @bbaird I updated the question with the expected output.
    – Raida Adn
    Aug 10 '21 at 6:03
  • @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?
    – Andriy M
    Aug 10 '21 at 12:49
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.