sql select更新多列并在更新前检查不为空

Question

我有两个表。
表1结构NOT NULL。
表2的某些值为NULL。
我想要更新table2到table1
我希望当选择值，如果null不更新，并跳过下一列有更新值。

我的表

table1  value(table1)  table2  value(table2) 
t1ID  1234    t2ID   1234 
t1Name  Bear    t2Name  null 
t1Adress 87/25    t2Adress  99/77 
t1Tel  [phone]  t2Tel  null 

我的代码

UPDATE table1 
SET t1Name = (SELECT t2Name 
    FROM table2 
    WHERE t2Name IS NOT NULL 
    ), 
    t1Adress = (SELECT t2Adress 
    FROM table2 
    WHERE t2Adress IS NOT NULL 
    ), 
    t1Tel = (SELECT t2Tel 
    FROM table2 
    WHERE t2Tel IS NOT NULL 
    ) 
FROM table1,table2 
WHERE t1ID = '1234' AND t2ID ='1234' 

当我执行我得到错误：

SQL error : Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.

我怎样才能解决呢？

Answer 1

下面的查询与表1的值代替空值，并从表2值

UPDATE T1 SET T1.t1Name = IsNull(T2.t2Name,T1.t1Name), 
       T1.t1Adress = IsNull(T2.t2Adress,T1.t1Adress), 
       T1.t1Tel = IsNull(T2.t2Tel,T1.t1Tel) 
     FROM table1 TI 
       INNER JOIN table2 T2 ON T1.t1ID = T2.t2ID 

尝试下面的选择查询，以检查你的结果

SELECT T1.t1Name,IsNull(T2.t2Name,T1.t1Name),T1.t1Adress,IsNull(T2.t2Adress,T1.t1Adress),T1.t1Tel,IsNull(T2.t2Tel,T1.t1Tel) 
     FROM table1 TI 
       INNER JOIN table2 T2 ON T1.t1ID = T2.t2ID 

Answer 2

我想你打算：

UPDATE t1 
    SET t1Name = COALESCE(t2.t2Name, t1.t1Name), 
     t1Adress = COALESCE(t2.t2Adress, t1.t1Adress), 
     t1Tel = COALESCE(t2.t2Tel, t1.t2Tel) 
    FROM table1 t1 JOIN 
     table2 t2 
     ON t1.t1id = t2.t2id 
    WHERE t1.t1ID = 1234; 

请注意，我取消了'1234'单引号。 Id通常是数字，所以他们应该与数字进行比较。

您的代码失败，因为您使用子查询而不是join中的值。您似乎在table2中有多个行，因此您得到的子查询返回了多个行错误。