在下面的兩個表中，對于學校名稱相同的學生和家長：我希望將 parent_name 分配給 student_name。
CREATE TABLE students2 (
student_id CHAR(2),
student_name VARCHAR2(30),
student_grade NUMBER(2),
school_name VARCHAR2(50)
)
CREATE TABLE parents (
student_id CHAR(2),
parent_name VARCHAR2(30),
school_name VARCHAR2(50)
)
UPDATE students2
SET student_name = (SELECT parent_name
FROM parents
WHERE parents.school_name = students2.school_name)
當我撰寫此查詢時，更新已完成，但會生成不符合條件的 student_names NULL。他們不應該改變。
uj5u.com熱心網友回復：
您需要說明應該更新哪些行；見WHERE EXISTS。
UPDATE students2 a SET
a.student_name = (SELECT b.parent_name
FROM parents b
WHERE b.school_name = a.school_name)
WHERE EXISTS (SELECT NULL
FROM parents c
WHERE c.school_name = a.school_name);
或者，因為你還沒有學到EXISTS：
UPDATE students2 a SET
a.student_name = (SELECT b.parent_name
FROM parents b
WHERE b.school_name = a.school_name)
WHERE a.school_name IN (select c.school_name
FROM parents c);
uj5u.com熱心網友回復：
根據我的理解，如果您想將 parent_name 分配給 student_name，查詢不應該像-
UPDATE students2 SET student_name = (SELECT parent_name FROM parents WHERE parents.student_id= students2.student_id)
無論如何，如果我遺漏了一些東西或者你已經知道了，也許你可以為空值嘗試這個：
UPDATE students2 SET student_name =(SELECT parent_name FROM parents
WHERE
CASE
WHEN %s IS NOT NULL
< statements >
ELSE < statements >
END
[AND/OR] <other constraints here> )
如果我錯了，請糾正我
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/342400.html
標籤：mysql sql更新 oracle-sqldeveloper
上一篇：MySQL觸發器是要走的路嗎？