All Forums Database
coffee 3 posts Joined 07/12
02 Aug 2012
Merge syntax (simple version)

I'm struggling to get a merge statement put together.  I'm using teradata release 13.10.01.02.

I want to do a merge without using a second table.  Please help!

 

I want the insert to do this:

    Insert Into load_DeviceMaster (
    downloadid,
    date_ ,
    serialnumber ,
    model ,
    productfamily ,
    precomments ,
    UserSettingDev ,
    UserSettingDevApproval ,
    UserSettingDevComments
    ) values (
    '17b40a2e-4d93-4590-b75c-9311dc324149',
    '2012-8-3 18:30:12',
    '12535353',
    'mymodel',
    'LP12',
    'Let''s have some comments',
    1,
    1,
    'What''s the comment?'
    );

And the update to do this:

Update set serialnumber = '123'

---------------------------

Below is what I came up with so far but it fails.

ERROR: [Teradata][ODBC Teradata Driver][Teradata Database] Column/Parameter
'DEV_LOAD.dm.dm' does not exist.
Error Code:
-3810

merge into load_DeviceMaster as dm
using ( select '17b40a2e-4d93-4590-b75c-9311dc324149' as "downloadid" , '123' as "serial") as d
on dm.downloadid = d.downloadid
when matched then
update set dm.serialnumber = d.serial
when not matched then
    Insert  (
    downloadid,
    date_ ,
    serialnumber ,
    model ,
    productfamily ,
    precomments ,
    UserSettingDev ,
    UserSettingDevApproval ,
    UserSettingDevComments
    ) values (
    '17b40a2e-4d93-4590-b75c-9311dc324149',
    '2012-8-3 18:30:12',
    '12535353',
    'mymodel',
    'LP12',
    'Let''s have some comments',
    1,
    1,
    'What''s the comment?'
    )

 

 

ulrich 816 posts Joined 09/09
02 Aug 2012

is this what you are looking for?

create table merge_tmp (a integer, b char(10), c char(10)) unique primary index (a);
insert into merge_tmp values (1,'abc','def');

SELECT * from merge_tmp
;

merge into merge_tmp as t using (select 1 as a,'stf' as b,'uuj' as c)  as s
on t.a = s.a
when matched then update set c = s.c
when not matched then insert values (s.a,s.b,s.c);

merge into merge_tmp as t using (select 2 as a,'stf' as b,'uuj' as c)  as s
on t.a = s.a
when matched then update set c = s.c
when not matched then insert values (s.a,s.b,s.c);

SELECT * from merge_tmp
;

drop table merge_tmp;

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

You must sign in to leave a comment.