Pl sql updating tables from another table
UPDATE Table1 SET (...) WHERE Column1='Some Value'IF @@ROWCOUNT=0 INSERT INTO Table1 VALUES (...) The saved table/index scan can increase performance quite a bit as the number of rows in the targeted table grows.
Just remember, the examples in the MSDN documentation are usually the easiest way to implement something, not necessarily the best way.
update existing rows in a table or insert new rows depending on a match condition.
What I need to do is choose the record with the max “Eff Date” from “Table A” for a particular “Emp No.” and update the “Desc” from that record in the field “Desc” of “Table B” for the same “Emp No.”. (More of Maxim’s solutions may be found in the “SQL – Methods of Reformatting into Equivalent Forms” blog series.) ******************** When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes.
GROUPING) IN ( SELECT EMP_NO, GROUPING FROM (SELECT EMP_NO, GROUPING, ROW_NUMBER() OVER (PARTITION BY EMP_NO, GROUPING ORDER BY EFF_DATE DESC) RN, DESCR FROM T1) WHERE RN=1); 2 rows updated. GROUPING) IN ( SELECT EMP_NO, GROUPING FROM (SELECT EMP_NO, GROUPING, ROW_NUMBER() OVER (PARTITION BY EMP_NO, GROUPING ORDER BY EFF_DATE DESC) RN, DESCR FROM T1) WHERE RN=1); SELECT * FROM TABLE(DBMS_XPLAN. If that is true, then both, row_number and first/last can pick up any descr from the set of rows, in this case the sort key should be extended to identifiy rows uniquely.
Regarding different results – i can’t test it at the moment, but most likely, the sort order can’t uniquely identify rows in your test data (that means, there should be many rows with the same emp_no , grouping and eff_date and different descr values).
Also (as I re-learned recently), with any database operation, it is good to performance test the different approaches that you take.
Sometimes the method that you think would be the worst might actually outperform the way that you think would be the better way.