SQL:about Update 2 Tables issue

SQL:about Update 2 Tables issue

表A : name_a code_a
  李 li
  王 wang
  张 zhang
  赵 zhao

表B ： name_b code_b
  王 NULL
  王 NULL
  张 NULL
想把表B的code_b字段按 名字 自动添加成 表A 对应的code
如这个效果 name_b code_b:
  王 wang
  王 wang
  张 zhang

create table DD_A(name_a nvarchar(10),code_a varchar(10))

insert into DD_A select 'LI','li0'

insert into DD_A select 'WANG','wang0'

insert into DD_A select 'ZHANG','zhang0'

insert into DD_A select 'ZHAO','zhao0'

create table DD_B(name_b nvarchar(10),code_b nvarchar(10))

insert into DD_B select 'WANG',NULL

insert into DD_B select 'WANG',NULL

insert into DD_B select 'ZHANG',NULL

Solution:

1, update b 

   set code_b=a.code_a from 表B b 

   inner join 表A a 

   on a.name_a=b.name_b

result:ok

2, update b 

   set code_b=a.code_a

   from a 

   where a.name_a=b.name_b

result:ok

3, udpate B 

   set B.code_b=A.code_a 

   from A,B 

   where A.name_a=B.name_b

result:ok

4, update b 

   set b.code_b=(select a.code_a 

                 from a 

                 where a.name_a=b.name_b)

result:ok

Expansion1:

create table DD_A(id_a int, name_a nvarchar(10),code_a varchar(10),price_a int, date_a datetime)

insert into DD_A select 101,'LI','li0',1001,'2011-1-1'

insert into DD_A select 102,'WANG','wang0',1002,'2011-2-2'

insert into DD_A select 103,'ZHANG','zhang0',1003,'2011-3-3'

insert into DD_A select 104,'ZHAO','zhao0',1004,'2011-4-4'

create table DD_B(id_b int,name_b nvarchar(10),code_b nvarchar(10),price_b int, date_b datetime)

insert into DD_B select 102,'WANG',NULL,1002,'2011-2-2'

insert into DD_B select 102,'WANG',NULL,1022,'2011-2-22'

insert into DD_B select 103,'ZHANG',NULL,1003,'2011-3-3'

 

Before update:

select * from DD_A

SELECT * FROM DD_B

After update:

SELECT * FROM DD_B

 

Solution:

1, update b

   set code_b=a.code_a

   from DD_B b

   inner join DD_A a

   on a.name_a=b.name_b

   where b.price_b>1002 and b.date_b> '2011-2-2'

result:ok

update b

   set code_b=a.code_a

   from DD_B b

   inner join DD_A a

   on a.name_a=b.name_b

   and b.price_b>1002 and b.date_b> '2011-2-2'

result:ok

2, update DD_B

   set code_b=a.code_a

   from DD_A a

   where a.name_a= DD_B.name_b

   and DD_B.price_b>1002 and DD_B.date_b> '2011-2-2'

result:ok

3, update b

   set b.code_b=a.code_a

   from DD_A a,DD_B b

   where a.name_a=b.name_b

   and b.price_b>1002 and b.date_b> '2011-2-2'

result:ok

4, update DD_B 

  set code_b=(select code_a

              from DD_A a

              where a.name_a=DD_B.name_b)

  where DD_B.price_b>1002 and DD_B.date_b> '2011-2-2'

result:ok

Expansion2:

create table DD_A(id_a int, name_a nvarchar(10),code_a varchar(10),price_a int, date_a datetime)

insert into DD_A select 101,'LI','li0',1001,'2011-1-1'

insert into DD_A select 102,'WANG','wang0',1002,'2011-2-2'

insert into DD_A select 103,'ZHANG','zhang0',1003,'2011-3-3'

insert into DD_A select 104,'ZHAO','zhao0',1004,'2011-4-4'

create table DD_B(id_b int,name_b nvarchar(10),code_b nvarchar(10),price_b int, date_b datetime)

insert into DD_B select 102,'WANG',NULL,1002,'2011-2-2'

insert into DD_B select 102,'WANG',NULL,1022,'2011-2-22'

insert into DD_B select 103,'ZHANG',NULL,1003,'2011-3-3'

create table DD_C(id_b int,name_c nvarchar(10),code_c nvarchar(10),price_c int, date_c datetime)

insert into DD_C select 102,'ZHANG',NULL,1002,'2011-2-2'

insert into DD_C select 105,'ZHAO',NULL,1022,'2011-5-5'

 

Before update:

select * from DD_A

SELECT * FROM DD_B

SELECT * FROM DD_C

After update:

SELECT * FROM DD_B

Solution:

1, update b

   set code_b=a.code_a

   from DD_B b

   inner join DD_A a

   on a.name_a=b.name_b

   WHERE b.price_b in (select price_c from DD_C)

   and b.date_b> '2011-2-2'

result:ok

update b

   set code_b=a.code_a

   from DD_B b

   inner join DD_A a

   on a.name_a=b.name_b

   and b.price_b in (select price_c from DD_C)

   and b.date_b> '2011-2-2'

result:ok

2, update DD_B

   set code_b=a.code_a

   from DD_A a

   where a.name_a= DD_B.name_b

   and DD_B.price_b in (select price_c from DD_C) 

   and DD_B.date_b> '2011-2-2'

result:ok

3, update b

   set b.code_b=a.code_a

   from DD_A a,DD_B b

   where a.name_a=b.name_b

   and b.price_b in (select price_c from DD_C) 

   and b.date_b> '2011-2-2'

result:ok

4, update DD_B 

  set code_b=(select code_a

              from DD_A a

              where a.name_a=DD_B.name_b)

  where DD_B.price_b in (select price_c from DD_C) 

  and DD_B.date_b> '2011-2-2'

result:ok

Application Example:

111 result:?

update ad_space_media_buy

set  ad_space_media_buy.rate=ad_space.buy_price

from ad_space_media_buy

inner join ad_space

on  ad_space.ID=ad_space_media_buy.ad_space_id

where  ad_space_media_buy.rate<10 and ad_space_media_buy.rate>0

and ad_space_media_buy.status=1

and ad_space_media_buy.insert_time>=to_date('2012-1-18','yyyy-mm-dd')

and ad_space_media_buy.media_buy_id in

   (   select media_buy_id

     from media_buy_campaign

    where status>0

               and campaign_id in(select campaign_id  from campaign  where CAMPAIGN_FROM=0

                     and status>0 )

  )

 

222 result:fail

update ad_space_media_buy

set

ad_space_media_buy.rate=ad_space.buy_price

from ad_space

where  ad_space.ID=ad_space_media_buy.ad_space_id

and ad_space_media_buy.rate<10 and ad_space_media_buy.rate>0

and ad_space_media_buy.status=1

and ad_space_media_buy.insert_time>=to_date('2012-1-18','yyyy-mm-dd')

and ad_space_media_buy.media_buy_id in

 (   select media_buy_id

     from media_buy_campaign

    where status>0

               and campaign_id in(

                      select campaign_id

                     from campaign

                    where CAMPAIGN_FROM=0

                     and status>0

                       )

 ) ;

 

333 result: fail

update ad_space_media_buy

set

ad_space_media_buy.rate=ad_space.buy_price

from ad_space_media_buy ,ad_space

where  ad_space.ID=ad_space_media_buy.ad_space_id

and ad_space_media_buy.rate<10 and ad_space_media_buy.rate>0

and ad_space_media_buy.status=1

and ad_space_media_buy.insert_time>=to_date('2012-1-18','yyyy-mm-dd')

and ad_space_media_buy.media_buy_id in

 (   select media_buy_id

     from media_buy_campaign

    where status>0

               and campaign_id in(

                      select campaign_id

                     from campaign

                    where CAMPAIGN_FROM=0

                     and status>0

                       )

 ) ;

444 result:?

update ad_space_media_buy

set

ad_space_media_buy.rate=(select  ad_space.buy_price

                                                    From ad_space

                                                     Where ad_space.ID=ad_space_media_buy.ad_space_id)

Where ad_space_media_buy.rate<10 and ad_space_media_buy.rate>0

and ad_space_media_buy.status=1

and ad_space_media_buy.insert_time>=to_date('2012-1-18','yyyy-mm-dd')

and ad_space_media_buy.media_buy_id in

 (   select media_buy_id

     from media_buy_campaign

    where status>0

               and campaign_id in(

                      select campaign_id

                     from campaign

                    where CAMPAIGN_FROM=0

                     and status>0

                       )

 ) ;