Question about firebird!

#1

CREATE TABLE XX ( ID_ INTEGER NOT NULL,
XXN_ VARCHAR(20),
XXK_ VARCHAR(30) );

I want to insert data into table XX, if has ID_ then update XXN_ and
XXK_ else insert a new row.

How to do this?
Thanks!

--

#2

On 05.05.2008 16:26, db.geek wrote:

Quote:

CREATE TABLE XX ( ID_ INTEGER NOT NULL,
XXN_ VARCHAR(20),
XXK_ VARCHAR(30) );

I want to insert data into table XX, if has ID_ then update XXN_ and
XXK_ else insert a new row.

How to do this?

How come a db.geek doesn't know?

Basically you have two options:

1. insert, catch error and update

2. update, check update row count and insert if 0

Which of the two you choose depends on the RDBMS used as well as your
expected distribution of data. IOW if you expect that most data sets
will lead to an insert do approach 1 otherwise do 2.

Also, there can be subtle issues with concurrent updates in scenario 2,
i.e. the update can return 0 rows and the insert can still fail.

Btw, why the strange naming of columns?

Cheers

robert

#3

On 05.05.2008 16:26, db.geek wrote:

Quote:

CREATE TABLE XX ( ID_ INTEGER NOT NULL,
XXN_ VARCHAR(20),
XXK_ VARCHAR(30) );

I want to insert data into table XX, if has ID_ then update XXN_ and
XXK_ else insert a new row.

How to do this?

How come a db.geek doesn't know?

Basically you have two options:

1. insert, catch error and update

2. update, check update row count and insert if 0

Which of the two you choose depends on the RDBMS used as well as your
expected distribution of data. IOW if you expect that most data sets
will lead to an insert do approach 1 otherwise do 2.

Also, there can be subtle issues with concurrent updates in scenario 2,
i.e. the update can return 0 rows and the insert can still fail.

Btw, why the strange naming of columns?

Cheers

robert

#4

On 05.05.2008 16:26, db.geek wrote:

Quote:

CREATE TABLE XX ( ID_ INTEGER NOT NULL,
XXN_ VARCHAR(20),
XXK_ VARCHAR(30) );

I want to insert data into table XX, if has ID_ then update XXN_ and
XXK_ else insert a new row.

How to do this?

How come a db.geek doesn't know?

Basically you have two options:

1. insert, catch error and update

2. update, check update row count and insert if 0

Which of the two you choose depends on the RDBMS used as well as your
expected distribution of data. IOW if you expect that most data sets
will lead to an insert do approach 1 otherwise do 2.

Also, there can be subtle issues with concurrent updates in scenario 2,
i.e. the update can return 0 rows and the insert can still fail.

Btw, why the strange naming of columns?

Cheers

robert

#5

db.geek, 05.05.2008 16:26:

Quote:

CREATE TABLE XX ( ID_ INTEGER NOT NULL,
XXN_ VARCHAR(20),
XXK_ VARCHAR(30) );

I want to insert data into table XX, if has ID_ then update XXN_ and
XXK_ else insert a new row.

How to do this?
Thanks!

Firebird 2.1 has a MERGE statement which is what you want.

I think 2.0 already had an UPDATE OR INSERT statement.

Thomas

#6

db.geek, 05.05.2008 16:26:

Quote:

CREATE TABLE XX ( ID_ INTEGER NOT NULL,
XXN_ VARCHAR(20),
XXK_ VARCHAR(30) );

I want to insert data into table XX, if has ID_ then update XXN_ and
XXK_ else insert a new row.

How to do this?
Thanks!

Firebird 2.1 has a MERGE statement which is what you want.

I think 2.0 already had an UPDATE OR INSERT statement.

Thomas

#7

db.geek, 05.05.2008 16:26:

Quote:

CREATE TABLE XX ( ID_ INTEGER NOT NULL,
XXN_ VARCHAR(20),
XXK_ VARCHAR(30) );

I want to insert data into table XX, if has ID_ then update XXN_ and
XXK_ else insert a new row.

How to do this?
Thanks!

Firebird 2.1 has a MERGE statement which is what you want.

I think 2.0 already had an UPDATE OR INSERT statement.

Thomas

#8

[newblue@bEyoOo ~]$ cat Robert Klemme

Quote:

How come a db.geek doesn't know?

-_-

I'm a db beginner, I hope be come a db geek.

Quote:

Basically you have two options:

1. insert, catch error and update

2. update, check update row count and insert if 0

Now, I'm do it with this way. I post this topic, it is want to look for
a way better than this way, do it fast.

Quote:

Which of the two you choose depends on the RDBMS used as well as your
expected distribution of data. IOW if you expect that most data sets
will lead to an insert do approach 1 otherwise do 2.

Also, there can be subtle issues with concurrent updates in scenario
2, i.e. the update can return 0 rows and the insert can still fail.

Btw, why the strange naming of columns?

Cheers

robert

--
»úÆ÷ÖØÐÂ¿ª¶¯£¬ÏµÍ³ÖØÐÂ°²×°£¬½¨Á¢ÐÂµÄË¼Ïë£¬Ó*½ÓÐÂµÄ ¾ÖÃæ£¬Ò»ÇÐÖØÐÂÔÙÀ´
Èê²»¿ÉÒòÄÑ¶øÍý×Ô±¡·Æ

#9

[newblue@bEyoOo ~]$ cat Robert Klemme

Quote:

How come a db.geek doesn't know?

-_-

I'm a db beginner, I hope be come a db geek.

Quote:

Basically you have two options:

1. insert, catch error and update

2. update, check update row count and insert if 0

Now, I'm do it with this way. I post this topic, it is want to look for
a way better than this way, do it fast.

Quote:

Which of the two you choose depends on the RDBMS used as well as your
expected distribution of data. IOW if you expect that most data sets
will lead to an insert do approach 1 otherwise do 2.

Also, there can be subtle issues with concurrent updates in scenario
2, i.e. the update can return 0 rows and the insert can still fail.

Btw, why the strange naming of columns?

Cheers

robert

--
»úÆ÷ÖØÐÂ¿ª¶¯£¬ÏµÍ³ÖØÐÂ°²×°£¬½¨Á¢ÐÂµÄË¼Ïë£¬Ó*½ÓÐÂµÄ ¾ÖÃæ£¬Ò»ÇÐÖØÐÂÔÙÀ´
Èê²»¿ÉÒòÄÑ¶øÍý×Ô±¡·Æ

#10

[newblue@bEyoOo ~]$ cat Robert Klemme

Quote:

How come a db.geek doesn't know?

-_-

I'm a db beginner, I hope be come a db geek.

Quote:

Basically you have two options:

1. insert, catch error and update

2. update, check update row count and insert if 0

Now, I'm do it with this way. I post this topic, it is want to look for
a way better than this way, do it fast.

Quote:

Which of the two you choose depends on the RDBMS used as well as your
expected distribution of data. IOW if you expect that most data sets
will lead to an insert do approach 1 otherwise do 2.

Also, there can be subtle issues with concurrent updates in scenario
2, i.e. the update can return 0 rows and the insert can still fail.

Btw, why the strange naming of columns?

Cheers

robert

--
»úÆ÷ÖØÐÂ¿ª¶¯£¬ÏµÍ³ÖØÐÂ°²×°£¬½¨Á¢ÐÂµÄË¼Ïë£¬Ó*½ÓÐÂµÄ ¾ÖÃæ£¬Ò»ÇÐÖØÐÂÔÙÀ´
Èê²»¿ÉÒòÄÑ¶øÍý×Ô±¡·Æ