<div>
Run this and it should show you what IDs need to be changed from what to what. 
 
Then uncomment out the /* */ section to do the work. 

<pre><code class="code-10 nolinks" id="code-120788">create table #KeepDelete (Keep_Id int,Delete_Id int)
 
insert into #KeepDelete (Keep_Id,Delete_Id)
select keep.id as Keep_Id, delete.id as Delete_Id
(select id=min(id),field1,field2,field3 from TableA
group by field1,field2,field3) keep
join TableB delete
on keep.field1 = delete.field1
and keep.field2 = delete.field2
and keep.field3 = delete.field3
and keep.id &lt;&gt; delete.id
 
 
select * from #Keep_Delete
 
/*
update b
set a_id=kd.keep_id
from tableb b
join #keep_Delete kd
on b.id=kd.delete_id
 
delete from a
from tablea a
join #keep_delete kd
on a.id = kd.delete_id
*/
drop table #Keep_Delete
</code></pre>
</div>

<div>
there is quite a few errors with this.... not sure what you are trying to do to work out how to fix these
</div>

<div>
What are the errors? &nbsp;Are they invalid object name errors? &nbsp;Because it would appear as though they are generic table names in your posting and unless you change to the correct table/field names, it won't do anything.
</div>

<div>
trying to keep it simple at the moment so using the table names as specified.... 
 
quick review 
.. don't know what this is: keep.id, delete.id 
 
where are these tables from? 
 
is this line correct? 
 
join TableB delete 
 
 
--------the error as reported 
 
Server: Msg 156, Level 15, State 1, Line 4 
Incorrect syntax near the keyword 'delete'. 
Server: Msg 156, Level 15, State 1, Line 4 
Incorrect syntax near the keyword 'as'. 
Server: Msg 170, Level 15, State 1, Line 6 
Line 6: Incorrect syntax near 'keep'. 
Server: Msg 156, Level 15, State 1, Line 9 
Incorrect syntax near the keyword 'and'. 
Server: Msg 156, Level 15, State 1, Line 10 
Incorrect syntax near the keyword 'and'. 
Server: Msg 156, Level 15, State 1, Line 11 
Incorrect syntax near the keyword 'and'. 
 

</div>

<div>
Keep is a derived table and delete (now renamed to del since I was an idiot and aliased a table as a keyword) is just an alias for your table. 
 
Try it now. 

<pre><code class="code-10 nolinks" id="code-120820">create table #KeepDelete (Keep_Id int,Delete_Id int)
 
insert into #KeepDelete (Keep_Id,Delete_Id)
select keep.id as Keep_Id, del.id as Delete_Id
(select id=min(id),field1,field2,field3 from TableA
group by field1,field2,field3) keep
join TableB del
on keep.field1 = del.field1
and keep.field2 = del.field2
and keep.field3 = del.field3
and keep.id &lt;&gt; del.id
 
 
select * from #Keep_Delete
 
/*
update b
set a_id=kd.keep_id
from tableb b
join #keep_Delete kd
on b.id=kd.delete_id
 
delete from a
from tablea a
join #keep_delete kd
on a.id = kd.delete_id
*/
drop table #Keep_Delete
</code></pre>
</div>

<div>
nope... 
 
 
Server: Msg 170, Level 15, State 1, Line 6 
Line 6:Incorrect syntax near 'keep' 
 
This is SQL Server 2000 
 
I can't see how you can create a table called keep without alising it or creating it. 
 
i think I am starting to understand what you are trying to do ... but it does not work
</div>

<div>
the alias of del on TableB should be TableA... but what happens if fields contain a null value?
</div>

<div>
Which of the fields are nullable. &nbsp;You should use isnull on those, but select a value that will never exist in the table. &nbsp;So if the field is numeric, you wouldn't want to isnull it to 1 because 1 is likely to be used. &nbsp;If it is a char (nchar,nvarchar,char,varchar) field then you should isnull it to a string you know won't exist. 
 

</div>

<div>
<div class="content wysiwyg-content">
I have the following table 
 
TABLE A 
 
ID,FIELD1,FIELD2,FIELD3 
1,VALUE1,VALUE2,VALUE3 
2,VALUE1,VALUE2,VALUE3 
3,VALUE4,VALUE5,VALUE6 
 
TABLE B (where A_ID is related to TABLE A.ID) 
 
ID,A_ID,STATUS_ID 
1,1,1 
2,2,3 
 
Records ID 1 + 2 in table A are the same record so I would like to updated the table so that it looks like the following: 
 
TABLE A 
 
ID,FIELD1,FIELD2,FIELD3 
1,VALUE1,VALUE2,VALUE3 
3,VALUE4,VALUE5,VALUE6 
 
TABLE B 
 
ID,A_ID,STATUS_ID 
1,1,1 
2,1,3 
 
Removing the duplicate row but moving the related row to be related to record ID = 1 
 
How can I do this in T-SQL?
</div>
</div>

I have the following table

TABLE A

ID,FIELD1,FIELD2,FIELD3
1,VALUE1,VALUE2,VALUE3
2,VALUE1,VALUE2,VALUE3
3,VALUE4,VALUE5,VALUE6

TABLE B (where A_ID is related to TABLE A.ID)

ID,A_ID,STATUS_ID
1,1,1
2,2,3

Records ID 1 + 2 in table A are the same record so I would like to updated the table so that it looks like the following:

TABLE A

ID,FIELD1,FIELD2,FIELD3
1,VALUE1,VALUE2,VALUE3
3,VALUE4,VALUE5,VALUE6

TABLE B

ID,A_ID,STATUS_ID
1,1,1
2,1,3

Removing the duplicate row but moving the related row to be related to record ID = 1

How can I do this in T-SQL?

SQL Server T-SQL - Merge table and delete duplicates

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Microsoft SQL Server

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.