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?

asked 03 Jul '12, 09:32

Volker%20Barth's gravatar image

Volker Barth
30.8k308456665
accept rate: 32%

edited 07 Aug '12, 04:29

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...

(03 Jul '12, 09:36) Volker Barth

Any insights from the SQL/query processing group are still appreciated:)

(17 Jul '12, 03:45) Volker Barth
Replies hidden

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.

(17 Jul '12, 06:52) Martin

Look at the calendar. Canada has two weeks of summer every year, and this is it. Draw your own conclusions :)

(17 Jul '12, 07:51) Breck Carter

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?

(17 Jul '12, 10:00) Volker Barth

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.
In general, SQL Anywhere ignores any ORDER BY in a nested derived table if, for example, TOP clause is not present.

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 )
permanent link

answered 19 Jul '12, 18:15

Nica%20_SAP's gravatar image

Nica _SAP
866722
accept rate: 3%

edited 20 Jul '12, 09:49

Yes, this will work obviously.

So I assume that the underlying question is answered with

"No, you can't use an ORDER BY clause with MERGE as a top-level CLAUSE."

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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×107
×61
×29
×13
×6

question asked: 03 Jul '12, 09:32

question was seen: 2,825 times

last updated: 07 Aug '12, 04:29