/*
File name: sql_ex_update.txt
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!


*/

-- **************************************
-- Start: Build the test environment
-- **************************************

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

-- **************************
-- MyTable1
-- **************************

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

-- Table that has an ID that is created automatically.
create table MyTable1 ( id int IDENTITY(1,1) not null, code char(1), mytest nvarchar(255) )
go

-- Inserts where all you need to do is add a select.
insert into MyTable1 ( code, mytest ) 
  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), mytest nvarchar(255) )
go

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

select * from MyTable2

/*

*************************************
Update Examples
*************************************

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 a field with a static value.
update MyTable2 set mytest = null where code='A'
select * from MyTable2

-- Option #1 (using a join): Update a field in one table from another field in another table.
update t2
  set t2.mytest = t1.mytest 
  from MyTable2 t2
  left join MyTable1 t1 on t1.code = t2.code
  where 
    t2.code='A'

select * from MyTable2

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

-- Option #2: Update a field in one table from another field in another table.
update MyTable2
  set mytest = t1.mytest 
  from MyTable1 t1
  where 
    MyTable2.code='A'
    and 
    t1.code = 'A'

select * from MyTable2

/*
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 mytest = t2.mytest 
       from MyTable2 t2
       where 
         code='C'
         and 
         t2.code = 'A'
*/

update MyTable2 set mytest = null where code='C'

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

select * from #tmptable

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

drop table #tmptable

select * from MyTable1
select * from MyTable2