CODEWITHSUNDEEP
postgresqlinner-join
Raida Adn
Raida Adn

Reputation: 405

How to use a column value in one of the select statement in the join in postgresql

I'm updating sitename as below. But then I need to utilize the levelindex from a in another query which is getting joined with the first query.

update billofquantity_temp a
    set sitename = b.boqitemname 
from (
 select a.* from
 (
 select levelindex,productno,boqitemid,boqitemname,fileid from billofquantity_temp
                where productno ='' and levelindex !='' //first query
 ) a
    join 
 (
 select distinct substring(levelindex,1,length(a.levelindex) lblindex from billofquantity_temp
               where boqitemid !='' and levelindex !='' 
               and length(levelindex) > 2
 ) b    on a.levelindex=b.lblindex // second query 
) b
where a.levelindex=b.levelindex;

I need to use the a.levelindex in second query to get the substrings of the it's length. But this throws error invalid reference to FROM-clause entry for table "a"

Is there any other way to use the column from query 1 in 2 while joining them?

Update: Based on kims answer, I could get the selection done but there is error syntax error at c.a.levelindex. There error is thrown even with c.levelindex. A slight modification to the query made no errors but the intened update doesn't happen. Instead only one particular values is updated. Below is the updated query to remove errors

 update billofquantity_temp d
 set sitename = c.boqitemname
 from (
      select *  from (
            select levelindex, boqitemname from billofquantity_temp
            where productno = '' and levelindex != '' -- first query
                     ) a
                join (
            select distinct levelindex lblindex from billofquantity_temp
            where boqitemid != '' and levelindex != '' and length(levelindex) > 2   ) b
on a.levelindex = substring(b.lblindex, 1, length(a.levelindex)) -- second query
      ) c 
       where d.levelindex = c.lblindex;

Upvotes: 0

Views: 546

Answers (1)

Kim
Kim

Reputation: 461

Move the computation of lblindex outside the sub-select to the on clause:

update billofquantity_temp d
set sitename = c.boqitemname
from (
    select *
    from (
        select levelindex, boqitemname
        from billofquantity_temp
        where productno = '' and levelindex != '' -- first query
    ) a
    join (
        select distinct levelindex
        from billofquantity_temp
        where boqitemid != '' and levelindex != '' and length(levelindex) > 2     
    ) b
    on a.levelindex = substring(b.levelindex, 1, length(a.levelindex)) -- second query
) c 
where d.levelindex = c.a.levelindex
;

I've also used c and d to avoid confusion from using a and b twice, added a missing ), and removed unused fields from select.

Upvotes: 1

Related Questions