Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

populating null rows in table column based on matching IDs via join or otherwise

Hi all, quick question about updating a final table - i hope what i'm asking is clear.

Let's say i have two tables: Table A and Table B. Let's also say that Table A is my final/master table used for data vis within Tableau (or something akin), and that Table B feeds certain columns into Table A based on matches within a tertiary table, Table C (which is not relevant to this conversation).

As is, Table A has columns:

ProgramName [varchar(50)]
CustomerName [varchar(50)]
Total_Cost [numeric(18,4)]

As is, Table B has columns:

CustomerCode [varchar(10)]
Total_Cost [numeric(18,4)]

What I would like to do is update Table A's CustomerName column to equal CustomerCode in Table B where the columns of total_cost_dollars equal each other across tables.

I've run this left join query to ensure that, when I do update Table A's CustomerName to equal CustomerCode, the total cost columns are exact/true matches for my entire data set.

SELECT
A.ProgramName,
A.CustomerName,
A.total_cost_dollars,
B.CustomerCode,
B.total_cost_dollars
FROM
TableA A
LEFT JOIN
TableB B
ON
B.total_cost_dollars = A.total_cost_dollars
WHERE
A.CustomerName IS NULL;
Any quick help is much appreciated.

Thanks so much!

Answers

  • moshegmosheg Employee
    CREATE TABLE A (ProgramName varchar(50), CustomerName varchar(50), Total_Cost numeric(18,4))
    ORDER BY Total_Cost;
    CREATE TABLE
    
    CREATE TABLE B (CustomerCode varchar(10), Total_Cost numeric(18,4))
    ORDER BY Total_Cost;
    CREATE TABLE
    
    COPY A (ProgramName,CustomerName,Total_Cost) FROM STDIN DELIMITER ',' ABORT ON ERROR;
    prog1,,100
    prog2,Fred,200
    prog3,,300
    \.
    
    COPY B (CustomerCode, Total_Cost ) FROM STDIN DELIMITER ',' ABORT ON ERROR;
    Alex,100
    Dave,300
    Max,400
    \.
    
    SELECT * FROM A ORDER BY Total_Cost;
     ProgramName | CustomerName | Total_Cost
    -------------+--------------+------------
     prog1       |              |   100.0000
     prog2       | Fred         |   200.0000
     prog3       |              |   300.0000
    (3 rows)
    
    SELECT * FROM B ORDER BY Total_Cost;
     CustomerCode | Total_Cost
    --------------+------------
     Alex         |   100.0000
     Dave         |   300.0000
     Max          |   400.0000
    (3 rows)
    
    SELECT A.ProgramName, A.CustomerName, A.total_Cost, B.CustomerCode, B.total_Cost
    FROM A LEFT JOIN B
    ON B.Total_Cost = A.Total_Cost
    WHERE A.CustomerName IS NULL;
     ProgramName | CustomerName | total_Cost | CustomerCode | total_Cost
    -------------+--------------+------------+--------------+------------
     prog1       |              |   100.0000 | Alex         |   100.0000
     prog3       |              |   300.0000 | Dave         |   300.0000
    (2 rows)
    
    UPDATE A
       SET CustomerName = B.CustomerCode
       FROM B
       WHERE A.Total_Cost = B.Total_Cost;
     OUTPUT
    --------
          2
    (1 row)
    
    SELECT * FROM A ORDER BY Total_Cost;
     ProgramName | CustomerName | Total_Cost
    -------------+--------------+------------
     prog1       | Alex         |   100.0000
     prog2       | Fred         |   200.0000
     prog3       | Dave         |   300.0000
    (3 rows)
    

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.