SQL入门求助：两表PID匹配时，如何用关联计算值更新单表列？

Hey there! I see you're an SQL beginner trying to update the price column in your second table by multiplying PPRICE from the first table with quantity from second, using the matching PID column. Let's get this sorted for you!

Since your table structure uses VARCHAR2 (a clear sign you're working with Oracle), here are two straightforward methods to make this update happen:

方法1：带子查询的UPDATE语句 #

This is a simple approach that uses a subquery to fetch the matching PPRICE from the first table:

UPDATE second s
SET s.price = (SELECT f.PPRICE * s.quantity FROM first f WHERE f.PID = s.PID)
WHERE EXISTS (SELECT 1 FROM first f WHERE f.PID = s.PID);

• The WHERE EXISTS clause ensures we only update rows in second that have a matching PID in first—this prevents accidentally setting price to NULL for rows with no match.

方法2：使用MERGE语句（更灵活的关联更新） #

If you ever need to handle more complex scenarios (like inserting new rows and updating existing ones in one go), Oracle's MERGE statement is perfect:

MERGE INTO second s
USING first f
ON (s.PID = f.PID)
WHEN MATCHED THEN
  UPDATE SET s.price = f.PPRICE * s.quantity;

This statement matches rows from both tables using PID, then updates the price column in second with the calculated product.

Quick Tips to Avoid Issues #

• Make sure PID is a unique column in first (ideally a primary key)—this prevents the subquery from returning multiple rows, which would break the update.
• If PPRICE or quantity could ever be NULL, use the NVL function to handle empty values, like this: NVL(f.PPRICE, 0) * NVL(s.quantity, 0)—this ensures you don't end up with NULL in the price column.

first表结构：

Name	Null?	Type
PID	NOT NULL	NUMBER(38)
PNAME	NOT NULL	VARCHAR2(20)
PPRICE	NOT NULL	FLOAT

内容的提问来源于stack exchange，提问作者vignesh tokyo