bgm

MS SQL 可以用update from 

UPDATE titles
    SET ytd_sales = t.ytd_sales + s.qty
    FROM titles t, sales s
    WHERE t.title_id = s.title_id
    AND s.ord_date = (SELECT MAX(sales.ord_date) FROM sales)

但是ORACLE就只不支援 可以用 UPDATE table1 t_alias1 SET column1 = (SELECT expr FROM table2 t_alias2 WHERE t_alias1.column2 = t_alias2.column2); 但是些指令當table1中column2有table2裡沒有對應的資料，則column1會被update成null 再不然就只能用cursor Example 13-7 Using UPDATE With a Subquery -- Create a table with all the right IDs, but messed-up names CREATE TABLE employee_temp AS SELECT employee_id, UPPER(first_name) first_name, TRANSLATE(last_name,'aeiou','12345') last_name FROM employees; BEGIN -- Display the first 5 names to show they're messed up FOR person IN (SELECT * FROM employee_temp WHERE ROWNUM < 6) LOOP DBMS_OUTPUT.PUT_LINE(person.first_name || ' ' || person.last_name); END LOOP; UPDATE employee_temp SET (first_name, last_name) = (SELECT first_name, last_name FROM employees WHERE employee_id = employee_temp.employee_id); DBMS_OUTPUT.PUT_LINE('*** Updated ' || SQL%ROWCOUNT || ' rows. ***'); -- Display the first 5 names to show they've been fixed up FOR person IN (SELECT * FROM employee_temp WHERE ROWNUM < 6) LOOP DBMS_OUTPUT.PUT_LINE(person.first_name || ' ' || person.last_name); END LOOP; END;