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

mpc83mpc83 Vertica Customer

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 Vertica Employee Administrator
    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