CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008mergesql-merge
Annie
Annie

Reputation: 149

How can I fix the error : Multi-Part ID when update a field of @tempTable

I want to compare @tempTable with tableA, if matched then update FieldValue of @tempTable equal to id of tableA; if not matched then insert values ,and get the id of tableA to update FieldValue of @tempTable.

The following is my SQL query:

create table [dbo].[TableA]([Id] int Indentity(1,1),[Data] [sql_variant] NULL)

declare @tempTable table (FieldValue nvarchar(1000),FieldType nvarchar(1000))
insert @tempTable values ('some content','A Type') 

merge
    tableA
using (
    select 
        FieldValue
    from
        @tempTable
) x
on tableA.[Data] = x.FieldValue
when not matched then  
    insert values (x.FieldValue)
when matched then   
    update set x.FieldValue = tableA.[Id] ;

The following is the error message:

Unable to tie the Multi-Part ID "x.FieldValue".

The error looks like it's different data type between x.FieldValue and tableA.id, so I adjust them to the same data type, but it's still not work, and I don't know how to fix it.

Upvotes: 1

Views: 93

Answers (2)

Annie
Annie

Reputation: 149

The following can achieve the results I want.

merge
  tableA
using (
  select 
    FieldValue
  from
    @tempTable
) x
on tableA.[Data] = x.FieldValue
when not matched then
  insert values (x.FieldValue);

update @tempTable
set t.FieldValue = i.[Id]
from @tempTable t
  join TableA i ON i.[Data] = t.FieldValue

select * from @tempTable

Upvotes: 1

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

What are you trying to achieve? Your target table is tableA, your source table is the x-aliased sub-query.

Next time please try to create a mcve. I had to modify your code slightly, but you will get the ghist:

declare @TableA TABLE([Id] int,[Data] [sql_variant] NULL)

declare @tempTable table (FieldValue nvarchar(1000),FieldType nvarchar(1000))
insert @tempTable values ('some content','A Type'); 

merge
    @tableA a
using (
    select 
        FieldValue
    from
        @tempTable
) x
on a.[Data] = x.FieldValue
when matched then   
    update set a.id = x.FieldValue; --fields swapped, probably not really your intention...

The point is: Your code tries to update a field of your source table. The general idea of MERGE is

  • We target one table where we want to insert/update/delete some records.
  • We use a second set with the data we want to compare and use for these operations
  • We find rows within the source which are missing in the target -> INSERT
  • We find rows within the source which are existing in the target -> UPDATE
  • We find rows whithin the target which are missing in the source -> DELETE

Saying this, I doubt, that the code above would need MERGE at all...

Upvotes: 1

Related Questions