Morning

I got an odd one from work today. Can a MERGE statement both insert and update on a match?

The MERGE can have two WHEN MATCHED clauses, so theoretically yes. transactionname="UPDATE"

The better question is, why would you willingly use broken MERGE? sqlblog.org/merge

@ConcernedOfTunbridgeWells so if there is a match, you want to update the existing row and insert another one? I don't think that's valid in Postgres merge. Not sure about sql server implementation

@Zikato Not on the same row though. I have a feeling CoTW is asking if a single matched row can result in an insert and an update, to which the answer is no.

General rule is each row can qualify for at most one action.

ah ok, I misunderstood the question

@PaulWhite right, that's what I thought, too.

@Zikato It depends what the question was. You might be right. The question was ambiguous.

The potential to be right is good enough for me.

I can imagine a workaround, where the source rows are duplicated with an extra column (say marked with 'i' and 'u') and then we have a WHEN MATCHED AND source.extra ='i' to do the insert and a similar action for the update. And making sure that WHEN NOT MATCHED use one of the duplicated rows.

Yes you could also use the OUTPUT clause of the MERGE to drive an outer INSERT statement.

But the question implied just a single MERGE statement.

For example dbfiddle.uk/…

INSERT @T2
SELECT M.c1 + 100, 101
FROM
(
    MERGE @T2 AS T2
    USING @T3 AS T3
        ON T3.c1 = T2.c1
    WHEN MATCHED THEN UPDATE SET T2.c2 = 100
    OUTPUT Inserted.c1
) AS M;

But if you do that sort of thing you deserve everything you suffer imo

if you do that you will not have a great weekend

I defy anyone to remember the restrictions on composable DML

At some point, someone will add something that breaks one of those rules and your weekend will not be great indeed

The only defensible reason to use MERGE is on a heap where you want to see a full-colour showplan operator

I hope they never fix that

mr. blue

A chairde - Morning all!

Has anyone noticed any weird behaviour with dbfiddle lately? I'm finding that (on Windows), when you select the entire snippet, it blanks out the text - is this happening to anyone else?

Hallo consultants

I could use help finding what I'm sure is a book or blog that talks about how developers should approach database changes wrt services/apis that rely on them

I figured I can't really sort the googleterms required to magic this up, so I would try collective wisdom

Say that my developers are working on v1 of their application + schema. They want to add some column like return_ratio and for v2 of their application they want that value so they can use it

They say "ah-ha, when I release v2, I can include a migration [ed: we use FluentMigrator as we are a C# shop, and so that's how they get applied in environments beyond local dev] that will create a column with a default value of 0 in an update statement, and make it not-null, so that we always have the value"

Now, you all here understand exactly why that's a face-palm, I'm sure

Because v1.8 of the app may still need to be running in some user-facing environment, and this is a model change for v2, so it won't be included in v1 codebases

Aside from face planting 300 times to learn how to skateboard, as it were, can you all think of any blogs that talk about how you need 3 version release cycles (maybe it makes sense to talk in longer cycles) for things like deleting old schema whatevers?

Keep in mind we now live in the age of docker and k8s so there can be many versions of a service in use at one time, v1.8, v2.2, v3.0.1-alpha, ya know?

(also I have evil chaos monkey plans to just randomly apply migrations as they commit them to the QA environment to force the QA environment to break when devs do stupid shit, and also to not-apply them to dev environments for some period of time so that they also have to live with the pain of "why didn't my thing work?" because production is rarely so forgiving as WOMM)

Ask Erik, he the one who blogs. Maybe he can even blog that 👆

Some people use stored procs as the abstraction layer to hide schema changes. The app then calls sp_something_vX, where X is the app version

@jcolebrand off the top of my head this is the only post I can think of that might come close: michaeljswart.com/2018/01/…

Aside from that I think you’d be looking at books like designing database intensive applications and maybe site reliability engineering (for when deployments go bad)

But I hate developers so maybe don’t listen to me

TRIM ( [ LEADING | TRAILING | BOTH ] [characters FROM ] string )