How to update table content in MySQL?-CodePudding

This is my current SQL Database.

"customers"
|cust_id|cust_name         |cust_contact |
|-------|------------------|-------------|
|1      |John's Restaurant |123          |
|2      |Carlos's Pizza    |456          |
|3      |Midtown Cafe      |789          |
|4      |Selena HomeCook   |012          |

"invoice"
|inv_id     |cust_id        |amount      |
|-----------|---------------|------------|
|113        |2              |34.50       |
|114        |4              |105.90      |
|115        |1              |14.00       |

I am trying to make it into

"invoice"
|inv_id     |cust_name        |amount      |
|-----------|-----------------|------------|
|113        |Carlos's Pizza   |34.50       |
|114        |Selena HomeCook  |105.90      |
|115        |John's Restaurant|14.00       |

How can I do that with SQL query using update statement?

CodePudding user response：

You need to add cust_name column in Invoice table. As cust_id must be int type of, otherwise you need to alter cust_id int to varchar.

ALTER TABLE invoice
  ADD cust_name varchar(250) NULL

And then update value in table

UPDATE Invoice AS I
JOIN customers AS C ON I.cust_id = C.cust_id
SET I.cust_name = C.cust_name

CodePudding user response：

use this

select inv_id,cust_name,amount from invoice 
left join customers on customers.cust_id = invoice.cust_id

CodePudding user response：

If you want to update the table, use this:


UPDATE invoice 
SET  cust_id  = customers.cust_name 
FROM
    invoice I
INNER JOIN
    customers C
ON 
     I.cust_id = C.cust_id