我想根據兩個條件SET將價格調整為SOFTWARE其他值：
SOFTWARE以 M 開頭的名稱。SOFTWARE安裝在INSTALLER表中的
代碼：
SELECT N_SOF,NAMESOF FROM SOFTWARE;
N_SOF NAMESOF
---------- -------------------------
64 WINDOWS
70 WINDOWS
71 MAC
72 MAC
73 MAC
SELECT N_INS,N_SOF FROM INSTALLER;
N_INS N_SOF
---------- ----------
3 71
3 72
12 73
我在 SQL*PLUS (ORACLE) 中試過這個，但沒有用。
SQL> UPDATE SOFTWARE
2 SET PRICE= PRICE 100
3 INNER JOIN INSTALLER I ON I.NLOG=SOFTWARE.N_SOF
4 WHERE SOFTWARE.NAMESOF='M%' AND I.N_INS=3;
我收到此錯誤訊息
INNER JOIN INSTALLER I ON I.NLOG=LOGICIEL.NLOG
*
ERROR at line 3:
ORA-00933: SQL command not properly ended
uj5u.com熱心網友回復：
UPDATE SOFTWARE
SET PRICE= PRICE 100
WHERE
SOFTWARE.NAMESOF LIKE 'M%'
AND EXISTS (
SELECT *
FROM INSTALLER I
WHERE I.NLOG=SOFTWARE.N_SOF
AND I.N_INS=3
)
uj5u.com熱心網友回復：
使用UPDATE與EXISTS條款：
UPDATE SOFTWARE s
SET PRICE= PRICE 100
WHERE NAMESOF LIKE 'M%'
AND EXISTS(SELECT 1
FROM INSTALLER i
WHERE i.N_SOF=s.N_SOF
AND i.N_INS=3);
其中，對于樣本資料：
CREATE TABLE software (N_SOF, NAMESOF, price) AS
SELECT 64, 'WINDOWS', 0 FROM DUAL UNION ALL
SELECT 70, 'WINDOWS', 0 FROM DUAL UNION ALL
SELECT 71, 'MAC', 0 FROM DUAL UNION ALL
SELECT 72, 'MAC', 0 FROM DUAL UNION ALL
SELECT 73, 'MAC', 0 FROM DUAL;
CREATE TABLE INSTALLER (N_INS, N_SOF) AS
SELECT 3, 71 FROM DUAL UNION ALL
SELECT 3, 72 FROM DUAL UNION ALL
SELECT 12, 73 FROM DUAL;
然后更新后：
SELECT * FROM software
輸出：
N_SOF 名稱SOF 價格 64 視窗 0 70 視窗 0 71 蘋果電腦 100 72 蘋果電腦 100 73 蘋果電腦 0
db<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/407952.html
標籤：