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

mpc83 · May 2020

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!

mosheg · May 2020

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)

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

Answers

Leave a Comment