|
I'm modifying the contents of a table with "fresh data" and make use of the MERGE statement. I'm using something like merge into MyTable T (<column list>) using with auto name (select <some computed values> from MyOtherTable where -- someConditions order by col4) MOT on T.col2 = MOT.col2 and T.col3 = MOT.col3 and T.col4 = MOT.col4 when matched then skip when not matched then insert; So I'm obviously only inserting rows. As MyTable has a PK column (as col1) with DEFAULT AUTOINCREMENT, I would like to have the inserted rows in the same order as in the source select statement - i.e. ORDER BY col4. But they are inserted somewhat randomly. So, in contrast to INSERT ... SELECT ... ORDER BY ...", MERGE seems to ignore the order of the source table, and I don't see a way to specify a further ORDER BY clause. What can I do? |
|
The MERGE execution plan must left outer join the MOT derived table with 'MyTable T' before any WHEN clause is processed, hence any order imposed in the MOT table maybe lost in this join. Please try this statement for your specific needs: insert into myTable T1 (<auto name="" columns="">)
(select TOP ALL MOT.* from (select <some computed="" values="">
from MyOtherTable
where -- someConditions) MOT left outer join myTable T
on T.col2 = MOT.col2 and T.col3 = MOT.col3 and T.col4 = MOT.col4
where T.<pk column=""> IS NULL --- these are the rows in MOT not matching any rows in T
order by col4 )
Yes, this will work obviously. So I assume that the underlying question is answered with
If this is correct, I'd like to suggest an additional ORDER BY CLAUSE as a MERGE statement enhancement, just like this is possible (at least as a SQL Anywhere vendor extension) with INSERT/UPDATE/DELETE.
(07 Aug '12, 04:28)
Volker Barth
|
FWIW, I'm using MERGE here instead of INSERT ON EXISTING SKIP as the latter would not work because the join condition isn't a primary key - cf. this FAQ...
Any insights from the SQL/query processing group are still appreciated:)
You could insert the col1 explicitly, it is a default autoincrement column but you can still override the values if you like. It won't change the insert order but the primary key values will be equal for identical rows.
Look at the calendar. Canada has two weeks of summer every year, and this is it. Draw your own conclusions :)
You have only two weeks of summer in Canada? (Yes, I'm aware you're talking about vacancies...)
FWIW, the current "summer" in Germany feels more like late November - cold and rainy:(
@Breck: And we won't talk about the weather in China, right?