现有 2张表table_a
id source_string cn jp
1 aaa blank blank table_b
id source_string language string
1 aaa cn bbb
1 aaa jp ccc更新表 table_aid source_string cn jp
1 aaa bbb cccsql语句要怎么写呢?
id source_string cn jp
1 aaa blank blank table_b
id source_string language string
1 aaa cn bbb
1 aaa jp ccc更新表 table_aid source_string cn jp
1 aaa bbb cccsql语句要怎么写呢?
update a set a.cn=b.string from table_a a,table_b b where a.source_string=b.source_string
update a set a.cn=b.string from table_a a,table_b b
where a.source_string=b.source_string and b.language='cn'update a set a.jp=b.string from table_a a,table_b b
where a.source_string=b.source_string and b.language='jp'
PS:你这表结构为什么这么设计?
SET a.cn = b.cn,
a.jp = b.jp
FROM tabel_a a
INNER JOIN
(
SELECT * FROM table_b m
PIVOT
(MAX(string) FOR [language] IN([cn], [jp])) n
) b
ON a.id = b.id
AND a.source_string = b.source_string
set cn=(select top 1 string from table_b where table_b.source_string=table_a.source_string and table_b.language='cn' ),
jp=(select top 1 string from table_b where table_b.source_string=table_a.source_string and table_b.language='jp' )
where source_string='aaa'
DROP TABLE TABLE_A
CREATE TABLE TABLE_A( ID INT , Source_String nvarchar(20) , cn char(20) ,jp char(20) )
INSERT INTO TABLE_A VALUES(1,'aaa',DEFAULT,DEFAULT)SELECT * FROM TABLE_A
GO
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='TABLE_B')
DROP TABLE TABLE_B
CREATE TABLE TABLE_B(ID INT , Source_String nvarchar(20) , cn char(20) ,jp char(20) )INSERT INTO TABLE_B VALUES(1,'aaa','cn','bbb')INSERT INTO TABLE_B VALUES(1,'aaa','jp','ccc')
SELECT * FROM TABLE_BGOupdate a set a.cn=b.jp from table_a a,table_b b
where a.ID=b.ID and b.cn='cn'
update a set a.jp=b.jp from table_a a,table_b b
where a.ID=b.ID and b.cn='jp'
go
SELECT * FROM TABLE_A
DROP TABLE TABLE_A
CREATE TABLE TABLE_A( ID INT , Source_String nvarchar(20) , cn char(20) ,jp char(20) )
INSERT INTO TABLE_A VALUES(1,'aaa',DEFAULT,DEFAULT)SELECT * FROM TABLE_A
GO
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='TABLE_B')
DROP TABLE TABLE_B
CREATE TABLE TABLE_B(ID INT , Source_String nvarchar(20) ,language char(20) ,jp char(20) )INSERT INTO TABLE_B VALUES(1,'aaa','cn','bbb')INSERT INTO TABLE_B VALUES(1,'aaa','jp','ccc')
SELECT * FROM TABLE_BGOupdate a set a.cn=b.jp from table_a a,table_b b
where a.ID=b.ID and b.language='cn'
update a set a.jp=b.jp from table_a a,table_b b
where a.ID=b.ID and b.language='jp'
go
SELECT * FROM TABLE_A
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE name='TABLE_A')
DROP TABLE TABLE_A
CREATE TABLE TABLE_A( ID INT , Source_String nvarchar(20) , cn char(20) ,jp char(20) )
INSERT INTO TABLE_A VALUES(1,'aaa',DEFAULT,DEFAULT)
SELECT * FROM TABLE_A
GO
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='TABLE_B')
DROP TABLE TABLE_B
CREATE TABLE TABLE_B(ID INT , Source_String nvarchar(20) ,language char(20) ,jp1 char(20) )
INSERT INTO TABLE_B VALUES(1,'aaa','cn','bbb')
INSERT INTO TABLE_B VALUES(1,'aaa','jp','ccc')
SELECT * FROM TABLE_B
GO
UPDATE a SET a.cn = b.cn,a.jp = b.jp
FROM TABLE_A a
INNER JOIN
(
SELECT * FROM table_b
PIVOT(
MAX(jp1) FOR [language] IN([cn], [jp])
) m
) b
ON a.id = b.id AND a.source_string = b.source_string
SELECT * FROM TABLE_A