CODEWITHSUNDEEP
sqlpostgresql
Revathy
Revathy

Reputation: 3

To update data in a column based up on data from 2 other tables

I need to insert value into a column of a table based up on data from other tables. Example Data :

   Table ccdocs

    ID   index    reference     Location_id

    1    001       ABCD            
    2    001A      EFGH           
    3    002       NULL             
    4    003       NULL         



   Table: cclvig

   index   reference    Location

    001      ABCD       VMC     
    001A     EFGH       VMC_TOP    
    002      NULL       ICF      
    003      NULL       VMC


   Table : doc_location

  loc_id          Lctn

   1            VMC
   2            VMC_TOP
   3            ICF

All records of ccdocs are copied from cclvig through query. Now I have to insert location id into ccdocs depending on value from cclvig column "location". Table doc_location have location id. I tried update query with select statement.. but its returning multiple values.. please help..

Upvotes: 0

Views: 54

Answers (2)

Nitu Bansal
Nitu Bansal

Reputation: 3856

try below query

update ccdocs set Location_id=loc.loc_id

 from(
  select a.index,b.loc_id from  cclvig a inner join  doc_location on 
  a.Location=b.Lctn
 )loc
on ccdocs.index=loc.index

Upvotes: 0

Patrick
Patrick

Reputation: 32199

UPDATE ccdocs d
SET location_id = loc.loc_id
FROM doc_location loc
JOIN cclvig c ON c.location = loc.lctn
WHERE d.index = c.index;

In an UPDATE you can specify a query where to get new values from. This query is amlost idential to a regular SELECT statement (with restrictions on allowed clauses), but instead of the actual SELECT column list you have the UPDATE table SET column = phrase.

Upvotes: 1

Related Questions