/*
Author...: Michael Thomas 
email....: michael@michael-thomas.com
Date.....: 10/13/07
Modified.: 10/13/07

Description:  Example: Update 1 record (in MyTable2) from the value of another record in another table (in MyTable).
              Example: Update 1 record (in MyTable2) from the value of another record in the same table (in MyTable2). I found I had to create a Temp table!
              Example of using a field by the type of: "ntext".


*/

use master
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'MyTempDB') begin
  DROP DATABASE [MyTempDB]
end
go

create database MyTempDB
go

use MyTempDB

-- **************************
-- MyTable
-- **************************

if exists (select * from [dbo].[sysobjects] where id = object_id(N'MyTable') ) begin
  drop table MyTable
end
go

-- Table that has an ID that is created automatically.
create table MyTable ( id int IDENTITY(1,1) not null, code char(1), myntext ntext )
go

-- Inserts where all you need to do is add a select.
insert into MyTable ( code, myntext ) 
  select 'A', 'Hello World 1'
  union all
  select 'B', 'Hello World 2'
  union all
  select 'C', 'Hello World 3'

-- **************************
-- MyTable2
-- **************************

if exists (select * from [dbo].[sysobjects] where id = object_id(N'MyTable2') ) begin
  drop table MyTable2
end
go

-- Table that has an ID that is created automatically.
create table MyTable2 ( id int IDENTITY(1,1) not null, code char(1), myntext ntext )
go

-- Inserts where all you need to do is add a select.
insert into MyTable2 ( code, myntext ) 
  select code, myntext from MyTable

select * from MyTable2

/*
Subqueries in SQL Statements:

Example ERROR:
  * update MyTable2 set myntext = (select myntext from MyTable where code = 'A') where code='A'
  Server: Msg 279, Level 16, State 3, Line 1
  The text, ntext, and image data types are invalid in this subquery or aggregate expression.
*/

update MyTable2 set myntext = null where code='A'
select * from MyTable2

update MyTable2
  set myntext = t1.myntext 
  from MyTable t1
  where 
    MyTable2.code='A'
    and 
    t1.code = 'A'
   
update MyTable2 set myntext = null where code='C'

/*
Update a record table from another record in the same table.
  * From what I discovered, I had to use a temp table to store the results.
  *  The following DID NOT work!!!
     update MyTable2
       set myntext = t2.myntext 
       from MyTable2 t2
       where 
         code='C'
         and 
         t2.code = 'A'
*/

--Create a temporary table to hold the value.
create table #tmptable ( code char(1), myntext ntext )
delete #tmptable
go
insert into #tmptable 
  select code, myntext from MyTable2 where code = 'A'

select * from #tmptable

update MyTable2
  set myntext = tmp1.myntext 
  from #tmptable tmp1
  where 
    MyTable2.code='C'
    and 
    tmp1.code = 'A'

drop table #tmptable

select * from MyTable2
select * from MyTable