TQL UPDATE Statement to set a column values from another table using joins

I want to update a table's column (revenue) from another table by applying joins/Condition on the client name

 

Eg: 

UPDATE table2
SET New_field = "table1".revenue
from table1
INNER JOIN table1.Client = table2.Client;

2replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • You're wanting to update this at the table level? I think you'd have to do that prior to loading the data. You could probably accomplish something similar in a formula and create a view out of that data if that would help?

    Reply Like
  • There is a way you might be able to mimic, but that would only work if the 'Client' field is the primary key in both tables. If that is the case you could make use of the concept that all inserts are upserts in ThoughtSpot, so you could do something like:

    INSERT INTO table2 (
      Client
    , Revenue
    , <other fields from table 2>
    )
    SELECT
      table2.Client
    , table1.Revenue
    , <other fields from table 2>
    FROM table2, table1
    WHERE table1.Client = table2.Client;
    
    

    If this is on a production system, create a snapshot first!

    Reply Like
Like Follow
  • 1 mth agoLast active
  • 2Replies
  • 20Views
  • 3 Following