SQL bringing two tables into a existing table

I have two different tables and I want to bring over the data onto a single existing table that has columns for the inputs.

INSERT INTO `goac`.`customer`
(`CUST_ID`,
`CUST_NM`,
`CUST_INCOME_AM`,
`CUST_STREET_AD`,
`CUST_POSTAL_CD`,
`CUST_NO`,
`CUST_CITY_NM`,
`CUST_STATE_CD`,

`CUST_PHONE_NO`,
`CUST_BIRTH_DT`)

WHERE ODS_SALE_LARGE contains CUST_NM,CUST_STREET_AD,CUST_POSTAL_CD,CUST_NO,CUST_CITY_NM,CUST_STATE_CD, CUST_PHONE_NO,CUST_BIRTH_DT

and ODS_CUSTOMER has CUST_ID,CUST_NO,CUST_INCOME_AM

How do I bring these two tables together?

INSERT INTO `goac`.`customer`
(`CUST_ID`,
`CUST_NM`,
`CUST_INCOME_AM`,
`CUST_STREET_AD`,
`CUST_POSTAL_CD`,
`CUST_NO`,
`CUST_CITY_NM`,
`CUST_STATE_CD`,

`CUST_PHONE_NO`,
`CUST_BIRTH_DT`)

select distinct
ods_customer.*,
s.CUST_NM,
s.CUST_STREET_AD,s.CUST_POSTAL_CD,s.CUST_CITY_NM,s.CUST_STATE_CD,
s.CUST_PHONE_NO, s.CUST_BIRTH_DT
FROM ods_customer
JOIN ods_customer AS c
ON c.CUST_NO = customer.CUST_NO
join ods_sale_large as s
on s.CUST_NO = customer.CUST_NO

Answer

You don’t need to join ods_customer with itself. Just join it with ods_sales_large.

The join should be on the CUST_NM columns, since that’s the only thing in common between the two input tables.

You can’t reference customer table in the SELECT query, since that’s the table you’re inserting into, not where you’re selecting from.

INSERT INTO `goac`.`customer`
(`CUST_ID`, `CUST_NM`, `CUST_INCOME_AM`,
`CUST_STREET_AD`, `CUST_POSTAL_CD`, `CUST_NO`, `CUST_CITY_NM`, `CUST_STATE_CD`, `CUST_PHONE_NO`, `CUST_BIRTH_DT`)

select distinct
c.CUST_ID, c.CUST_NM, c.CUST_INCOME_AM,
s.CUST_STREET_AD, s.CUST_POSTAL_CD, s.CUST_NO, s.CUST_CITY_NM, s.CUST_STATE_CD, s.CUST_PHONE_NO, s.CUST_BIRTH_DT
FROM ods_customer AS c
join ods_sale_large as s
on s.CUST_NM = c.CUST_NM