USE tempdb GO IF OBJECT_ID('test') IS NOT NULL DROP TABLE test GO CREATE TABLE test( stuId INT PRIMARY KEY, stuName NVARCHAR(10), dname NVARCHAR(10) ) GO IF OBJECT_ID('department') IS NOT NULL DROP TABLE department GO CREATE TABLE department( did VARCHAR(10) PRIMARY KEY, dname NVARCHAR(10) ) GO SET NOCOUNT ON INSERT INTO test VALUES (1,'小明','计算机系') INSERT INTO test VALUES (2,'小红','化学系') INSERT INTO test VALUES (3,'小江','天文系') -- INSERT INTO department(did,dname) VALUES('A01','计算机系') INSERT INTO department(did,dname) VALUES('A02','化学系') -------- 以上为测试表及测试数据 --------------更新数据 UPDATE t SET t.dname = ISNULL(d.did,t.dname) FROM test AS t LEFT JOIN department AS d ON t.dname=d.dname --查看结果 SELECT * FROM test /* stuId stuName dname ----------- ---------- ---------- 1 小明 A01 2 小红 A02 3 小江 天文系 */
GO
IF OBJECT_ID('test') IS NOT NULL
DROP TABLE test
GO
CREATE TABLE test(
stuId INT PRIMARY KEY,
stuName NVARCHAR(10),
dname NVARCHAR(10)
)
GO
IF OBJECT_ID('department') IS NOT NULL
DROP TABLE department
GO
CREATE TABLE department(
did VARCHAR(10) PRIMARY KEY,
dname NVARCHAR(10)
)
GO
SET NOCOUNT ON
INSERT INTO test VALUES (1,'小明','计算机系')
INSERT INTO test VALUES (2,'小红','化学系')
INSERT INTO test VALUES (3,'小江','天文系')
--
INSERT INTO department(did,dname) VALUES('A01','计算机系')
INSERT INTO department(did,dname) VALUES('A02','化学系')
-------- 以上为测试表及测试数据 --------------更新数据
UPDATE t
SET t.dname = ISNULL(d.did,t.dname)
FROM test AS t LEFT JOIN department AS d ON t.dname=d.dname --查看结果
SELECT * FROM test
/*
stuId stuName dname
----------- ---------- ----------
1 小明 A01
2 小红 A02
3 小江 天文系
*/