[MySQL] product table - 이전 테이블의 값 중 하나만 가져와서 현재 테이블에 삽입

buy는 구매한 사람의 table

buy table의 price를 product에서 찾아 넣기!

 

//아래는 내가 한 방법

mysql> select*from buy;
+-----+----------+----------------+-------+--------+---------------------+
| num | customer | product_name   | price | amount | buy_date            |
+-----+----------+----------------+-------+--------+---------------------+
|   1 | 홍길동   | 폴라티셔츠     |     0 |      3 | 2023-03-24 17:22:02 |
|   2 | 홍길순   | 에어나시       |     0 |      5 | 2023-03-24 17:22:02 |
|   3 | 이순신   | 양털 겨울 코트 |     0 |      1 | 2023-03-24 17:22:02 |
+-----+----------+----------------+-------+--------+---------------------+
3 rows in set (0.00 sec)
mysql> update buy set price=
    -> (select price from product where name="폴라티셔츠")
    -> where product_name = '폴라티셔츠';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0
mysql> update buy set price=
    -> (select price from product where name="에어나시")
    ->  where product_name = '에어나시';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> update buy set price=
    -> (select price from product where name="양털 겨울 코트")
    ->  where product_name = '양털 겨울 코트';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from buy;
+-----+----------+----------------+-------+--------+---------------------+
| num | customer | product_name   | price | amount | buy_date            |
+-----+----------+----------------+-------+--------+---------------------+
|   1 | 홍길동   | 폴라티셔츠     | 15000 |      3 | 2023-03-24 17:22:02 |
|   2 | 홍길순   | 에어나시       |  9000 |      5 | 2023-03-24 17:22:02 |
|   3 | 이순신   | 양털 겨울 코트 | 50000 |      1 | 2023-03-24 17:22:02 |
+-----+----------+----------------+-------+--------+---------------------+
3 rows in set (0.00 sec)

 

// create 부터 찾아 넣는 방법 (선생님)

https://jungeun980906.tistory.com/86