是要这样? update a set 进价fjj =b.进价fdjj ,售价fsj =b.售价fdlsj from gfdjg a,tb8 b where a.货号fhh=b.货号jwsx# and b.分店号fdh=12
if object_id('tb8') is not null drop table tb8 go create table tb8 ( 货号jwsx# int, 进价fdjj numeric(9,1), 售价fdlsj numeric(9,1) ) go insert into tb8 select 43251,2.2,3.5 go if object_id('gfdjg') is not null drop table gfdjg go create table gfdjg ( 货号fhh int, 进价fjj numeric(9,1), 售价fsj numeric(9,1), 分店号fdh int ) go insert into gfdjg select 43251,null,null,3 union all select 43251,null,null,12 go update gfdjg set 进价fjj=tb8.进价fdjj,售价fsj=tb8.售价fdlsj from gfdjg inner join tb8 on gfdjg.货号fhh=tb8.货号jwsx# where 分店号fdh=12 go select * from gfdjg go /* 只有12号货店的才更新 货号fhh 进价fjj 售价fsj 分店号fdh ----------- --------------------------------------- --------------------------------------- ----------- 43251 NULL NULL 3 43251 2.2 3.5 12(2 行受影响) */
CREATE TABLE EMPA ( ID INT PRIMARY KEY NOT NULL, EMP_NAME VARCHAR(20) ); CREATE TABLE EMPB ( ID INT PRIMARY KEY NOT NULL, EMP_NAME VARCHAR(20) ) INSERT INTO EMPA VALUES(1,'A'); INSERT INTO EMPA VALUES(2,'B'); INSERT INTO EMPA VALUES(3,'C'); INSERT INTO EMPA VALUES(4,'E'); INSERT INTO EMPA VALUES(5,'F');INSERT INTO EMPB VALUES(3,'E'); INSERT INTO EMPB VALUES(4,'F'); INSERT INTO EMPB VALUES(5,'G');SELECT * FROM EMPA; SELECT * FROM EMPB;MERGE EMPA AS TARGET USING (SELECT * FROM EMPB) AS SOURCE ON (TARGET.ID = SOURCE.ID) WHEN MATCHED THEN UPDATE SET TARGET.EMP_NAME = SOURCE.EMP_NAME WHEN NOT MATCHED BY TARGET THEN INSERT (ID,EMP_NAME) VALUES(SOURCE.ID,SOURCE.EMP_NAME) WHEN NOT MATCHED BY SOURCE THEN DELETE ;SELECT * FROM EMPA; SELECT * FROM EMPB;
a
set
进价fjj =b.进价fdjj ,售价fsj =b.售价fdlsj
from
gfdjg a,tb8 b
where
a.货号fhh=b.货号jwsx#
tb8货号jwsx# 进价fdjj 售价fdlsj
43251 2.2 3.5
gfdjg货号fhh 进价fjj 售价fsj 分店号fdh
43251 2.2 3.5 3 12tb8表货号列跟gfdjg表的货号列对比,gfdjg表的分店号是12的,(gfdjg这个表有很多分店,所以要选分店号)如果相同,gfdjg表进价和售价就取tb8的进价和售价
update
a
set
进价fjj =b.进价fdjj ,售价fsj =b.售价fdlsj
from
gfdjg a,tb8 b
where
a.货号fhh=b.货号jwsx#
and b.分店号fdh=12
if object_id('tb8') is not null
drop table tb8
go
create table tb8
(
货号jwsx# int,
进价fdjj numeric(9,1),
售价fdlsj numeric(9,1)
)
go
insert into tb8 select 43251,2.2,3.5
go
if object_id('gfdjg') is not null
drop table gfdjg
go
create table gfdjg
(
货号fhh int,
进价fjj numeric(9,1),
售价fsj numeric(9,1),
分店号fdh int
)
go
insert into gfdjg
select 43251,null,null,3 union all
select 43251,null,null,12
go
update gfdjg set 进价fjj=tb8.进价fdjj,售价fsj=tb8.售价fdlsj from gfdjg inner join tb8 on gfdjg.货号fhh=tb8.货号jwsx# where 分店号fdh=12
go
select * from gfdjg
go
/*
只有12号货店的才更新
货号fhh 进价fjj 售价fsj 分店号fdh
----------- --------------------------------------- --------------------------------------- -----------
43251 NULL NULL 3
43251 2.2 3.5 12(2 行受影响)
*/
CREATE TABLE EMPA
(
ID INT PRIMARY KEY NOT NULL,
EMP_NAME VARCHAR(20)
);
CREATE TABLE EMPB
(
ID INT PRIMARY KEY NOT NULL,
EMP_NAME VARCHAR(20)
)
INSERT INTO EMPA VALUES(1,'A');
INSERT INTO EMPA VALUES(2,'B');
INSERT INTO EMPA VALUES(3,'C');
INSERT INTO EMPA VALUES(4,'E');
INSERT INTO EMPA VALUES(5,'F');INSERT INTO EMPB VALUES(3,'E');
INSERT INTO EMPB VALUES(4,'F');
INSERT INTO EMPB VALUES(5,'G');SELECT * FROM EMPA;
SELECT * FROM EMPB;MERGE EMPA AS TARGET
USING (SELECT * FROM EMPB) AS SOURCE
ON (TARGET.ID = SOURCE.ID)
WHEN MATCHED
THEN UPDATE SET TARGET.EMP_NAME = SOURCE.EMP_NAME
WHEN NOT MATCHED BY TARGET
THEN
INSERT (ID,EMP_NAME) VALUES(SOURCE.ID,SOURCE.EMP_NAME)
WHEN NOT MATCHED BY SOURCE
THEN DELETE ;SELECT * FROM EMPA;
SELECT * FROM EMPB;