Subject | Re: [Firebird-Architect] REPLACE, again |
---|---|

Author | Nando Dessena |

Post date | 2006-08-26T08:15:18Z |

Adriano,

A> This is what MERGE was invented for:

A> merge

A> into repositories r1

A> using delta_repositories r2

A> on (r1.repositoryName = r2.repositoryName)

A> when matched then

A> update set

A> repositoryName = r2.repositoryName,

A> schema = r2.schema,

A> sequenceName = r2.sequenceName,

A> filename = r2.filename,

A> rollovers = r2.rollovers,

A> currentVolumesystem = r2.currentVolumesystem

A> when not matched then

A> insert

A> (repositoryName, schema, sequenceName, filename,

A> rollovers, currentVolume)

A> values (r2.repositoryName, r2.schema, r2.sequenceName,

A> r2.filename, r2.rollovers, r2.currentVolume);

A> It do a "repositories right join delta_repositories on (...)", update

A> (in repositories) if the record exist in repositories and insert (in

A> repositories) if not.

A> You could change "delta_repositories" to a derived table.

A> I leave as an exercise to the reader to adapt it to run like REPLACE

A> with parameters.

I'm not sure I understand you. Is your point that the standard syntax

makes it difficult/cumbersome/impossible to implement with a

parameterized statement?

It seems to me that REPLACE is to MERGE what COALESCE (and NULLIF) is

to CASE: a special case common enough to justify a shortcut notation.

Is that correct?

Ciao

--

Nando Dessena

A> This is what MERGE was invented for:

A> merge

A> into repositories r1

A> using delta_repositories r2

A> on (r1.repositoryName = r2.repositoryName)

A> when matched then

A> update set

A> repositoryName = r2.repositoryName,

A> schema = r2.schema,

A> sequenceName = r2.sequenceName,

A> filename = r2.filename,

A> rollovers = r2.rollovers,

A> currentVolumesystem = r2.currentVolumesystem

A> when not matched then

A> insert

A> (repositoryName, schema, sequenceName, filename,

A> rollovers, currentVolume)

A> values (r2.repositoryName, r2.schema, r2.sequenceName,

A> r2.filename, r2.rollovers, r2.currentVolume);

A> It do a "repositories right join delta_repositories on (...)", update

A> (in repositories) if the record exist in repositories and insert (in

A> repositories) if not.

A> You could change "delta_repositories" to a derived table.

A> I leave as an exercise to the reader to adapt it to run like REPLACE

A> with parameters.

I'm not sure I understand you. Is your point that the standard syntax

makes it difficult/cumbersome/impossible to implement with a

parameterized statement?

It seems to me that REPLACE is to MERGE what COALESCE (and NULLIF) is

to CASE: a special case common enough to justify a shortcut notation.

Is that correct?

Ciao

--

Nando Dessena