How to REPLACE? 
Author Message
 How to REPLACE?

One approach would be to have a second table, say LOAD_CONTACTS
that had the same format.  The main difference would be that LOAD_CONTACTS would
have an insert trigger on it that would first delete all rows from CONTACTS that had
duplicate keys
with the INSERTED pseudo table, then insert CONTACTS select * from INSERTED, then
delete LOAD_CONTACT all rows from INSERTED.

Rather than REPLACE rows in CONTACTS, INSERT them into LOAD_CONTACTS.

-bret



Tue, 22 Apr 2003 03:00:00 GMT
 How to REPLACE?

You could use something like this to insert or update.

if exists (select * from < tablename > where col1 = cond1 and ...) begin
    update < tablename > set col1 = 'data',
                        col2 = 'data2',
                        ....
    where col1 = cond1 abd ...
end
else
begin
    insert < tablename > values ( 'data', 'data2', ...)
end



Quote:
> Hi,

> I have a table:

> create table CONTACTS
> (
>     CAA   char(12)    not null constraint check_caa check (CAA like
'CAA[ Z] %'),
>     REV   char(4)     not null constraint check_rev check (REV like 'R
[0-9]%'),
>     BLK   varchar(20) not null,
>     MHO   char(11)    not null,
>     NAME1 varchar(20) null,
>     MAIL1 varchar(60) null  /* constraint check_mail1 check (MAIL1

>     NAME2 varchar(20) null,
>     MAIL2 varchar(60) null  /* constraint check_mail2 check (MAIL2

>     UPD   datetime    not null,
>     FND   bit         not null,
>     constraint caa_rev primary key (CAA, REV)    /* the CAA + REV
pair is unique */
> )

> and regularly add new records to it witha Perl-script.

> Some of the new records are new (the combination of CAA
> and REV does not exist in the CONTACTS), but some are not.

> If I would be using MySQL, I'd call "REPLACE INTO CONTACTS
> VALUES (....)". But there is no such command in Transact-SQL.
> And when I call "INSERT CONTACTS VALUES (....)", then the Sybase
> complains of course about "Attempt to insert duplicate key
> row in object 'CONTACTS' with unique index 'caa_rev'", though
> I would like it just to silently replace the old records.

> So is there a way to do it in one pass and if not, how do
> you do it then?

> Thank you
> Alex

Sent via Deja.com http://www.deja.com/
Before you buy.


Tue, 22 Apr 2003 03:00:00 GMT
 How to REPLACE?
Hi,

I have a table:

create table CONTACTS
(
    CAA   char(12)    not null constraint check_caa check (CAA like 'CAA[ Z] %'),
    REV   char(4)     not null constraint check_rev check (REV like 'R[0-9]%'),
    BLK   varchar(20) not null,
    MHO   char(11)    not null,
    NAME1 varchar(20) null,

    NAME2 varchar(20) null,

    UPD   datetime    not null,
    FND   bit         not null,
    constraint caa_rev primary key (CAA, REV)    /* the CAA + REV pair is unique */
)

and regularly add new records to it witha Perl-script.

Some of the new records are new (the combination of CAA
and REV does not exist in the CONTACTS), but some are not.

If I would be using MySQL, I'd call "REPLACE INTO CONTACTS
VALUES (....)". But there is no such command in Transact-SQL.
And when I call "INSERT CONTACTS VALUES (....)", then the Sybase
complains of course about "Attempt to insert duplicate key
row in object 'CONTACTS' with unique index 'caa_rev'", though
I would like it just to silently replace the old records.

So is there a way to do it in one pass and if not, how do
you do it then?

Thank you
Alex



Tue, 22 Apr 2003 20:22:49 GMT
 How to REPLACE?

Quote:

> >So is there a way to do it in one pass and if not, how do
> >you do it then?

> UPDATE <tablename>
>  SET <columnname> = <expression>
>  [, <columnname> = <expression>]
> WHERE <expression>

Thanks, but if I am not mistaken this will only update the
existing records, but won't insert the new ones.

The MySQL's REPLACE would do both...



Tue, 22 Apr 2003 23:30:47 GMT
 How to REPLACE?
You could use something like this to insert or update.

if exists (select * from < tablename > where col1 = cond1 and ...)
begin
    update < tablename > set col1 = 'data',
                         col2 = 'data2',
                         ....
    where col1 = cond1 abd ...
end
else
begin
    insert < tablename > values ( 'data', 'data2', ...)
end



Quote:

> > >So is there a way to do it in one pass and if not, how do
> > >you do it then?

> > UPDATE <tablename>
> >  SET <columnname> = <expression>
> >  [, <columnname> = <expression>]
> > WHERE <expression>

> Thanks, but if I am not mistaken this will only update the
> existing records, but won't insert the new ones.

> The MySQL's REPLACE would do both...

Sent via Deja.com http://www.deja.com/
Before you buy.


Wed, 23 Apr 2003 00:17:03 GMT
 How to REPLACE?
Hi Bret,

thanks for your reply. I have spent today reading the O'Reilly
book on triggers and trying some approaches out and hope that
you or others will find some time to answer my questions below.

Quote:

> One approach would be to have a second table, say LOAD_CONTACTS
> that had the same format.  The main difference would be that LOAD_CONTACTS would
> have an insert trigger on it that would first delete all rows from CONTACTS that had
> duplicate keys
> with the INSERTED pseudo table, then insert CONTACTS select * from INSERTED, then

> Rather than REPLACE rows in CONTACTS, INSERT them into LOAD_CONTACTS.

Here is my original database:

create table CONTACTS
(  
    CAA   char(12)    not null constraint check_caa check (CAA like 'CAA[ Z] %'),
    REV   char(4)     not null constraint check_rev check (REV like 'R[0-9]%'),
    BLK   varchar(20) null,
    MHO   char(11)    null,
    NAME1 varchar(20) null,
    MAIL1 varchar(60) null,
    NAME2 varchar(20) null,
    MAIL2 varchar(60) null,
    UPD   datetime    not null,
    FND   bit         not null,
    constraint caa_rev primary key (CAA, REV)
)

I have tried first creating an INSERT-trigger for the CONTACTS

only single INSERT statements, I never call INSERT ... SELECT),
then this means the insert failed because of a duplicated CAA+REV
pair and an "update CONTACTS ... from inserted" should be run
instead. However this trigger is never being run, I guess because
the constraint check happens before the trigger, correct?

So then I have tried your suggestion:

create table LOAD_CONTACTS
(
    CAA   char(12)    not null constraint check_caa2 check (CAA like 'CAA[ Z] %'),
    REV   char(4)     not null constraint check_rev2 check (REV like 'R[0-9]%'),
    BLK   varchar(20) null,
    MHO   char(11)    null,
    NAME1 varchar(20) null,
    MAIL1 varchar(60) null,
    NAME2 varchar(20) null,
    MAIL2 varchar(60) null,
    UPD   datetime    not null,
    FND   bit         not null
)

create trigger emulate_replace
on LOAD_CONTACTS
for insert
as  
    delete CONTACTS
    from CONTACTS c, inserted i           /* delete duplicated entries first */
    where c.CAA = i.CAA and
          c.REV = i.REV

    insert CONTACTS
    select * from inserted

    delete LOAD_CONTACTS
    from LOAD_CONTACTS l, inserted i
    where l.CAA = i.CAA and
          l.REV = i.REV

I wonder however, why do you suggest

Quote:
> insert CONTACTS select * from INSERTED

and not from LOAD_CONTACTS? Is it because other processes (I'm going
to execute these commands from a CGI-script) will populate the table
LOAD_CONTACTS too? Then isn't it dangerous to what you recommend next:

Quote:
> delete LOAD_CONTACT all rows from INSERTED      ?

Can't it happen that I will delete some newer records, that were to
be inserted by the other processes (the other CGI-script invocations)?

I wonder if it wouldn't be better to have just 1 table - the CONTACTS
and drop the "constraint caa_rev primary key (CAA, REV)" and have an
INSERT-trigger for both checking the uniqueness and for replacing:

create table CONTACTS
(
    CAA   char(12)    not null constraint check_caa check (CAA like 'CAA[ Z] %'),
    REV   char(4)     not null constraint check_rev check (REV like 'R[0-9]%'),
    BLK   varchar(20) null,
    MHO   char(11)    null,
    NAME1 varchar(20) null,
    MAIL1 varchar(60) null,
    NAME2 varchar(20) null,
    MAIL2 varchar(60) null,
    UPD   datetime    not null,
    FND   bit         not null
)

create trigger replace_caa_rev
on CONTACTS
for insert
as
    delete CONTACTS
    from CONTACTS c, inserted i           /* delete duplicated entries first */
    where c.CAA = i.CAA and
          c.REV = i.REV

    insert CONTACTS
    select * from inserted

Do you see any flaws in this implementation and what will I lose,
when not having the "constraint caa_rev primary key (CAA, REV)"?

And is it safe when running by several simultaneous processes?

Regards
Alex



Fri, 25 Apr 2003 03:00:00 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. Replacing WITHOUT "replace"

2. Replace function Doesn't replace blanks in SQL 7 (No service packs?)

3. How to replace 'REPLACE' with a real SQL 'UPDATE' command

4. Replace the "Enter Parameter Value" dialog

5. Web SQL Database being replaced????

6. Need your advice: Strange output using REPLACE function and CURSOR to loop variable

7. Replacing sql2000

8. Replace function problems

9. Search and replace function

10. stored procedure and replace function

11. replacing patterns

12. REPLACE (T-SQL)


 
Powered by phpBB® Forum Software