Merge syntax (simple version)

Database
Fan

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?'

    )

Tags (1)
1 REPLY
Senior Supporter

Re: Merge syntax (simple version)

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;