如何使用单个查询更新单个表的多个列 [英] How to update multiple columns for a single table using single query

问题描述

嗨..

以下是我的表结构，

Hi..
Following is my table structure,

create table SampleTable1
(
Test1	int,
Test2	int,
Test3	int
)

我为三列插入值为（1,1,1），（2,2,2），（3,3,3）



现在我想用符号'' - ''更新两个3列值，我尝试使用以下查询作为

I inserted values for three columns as (1,1,1),(2,2,2),(3,3,3)

Now i want to update both 3 columns values with the symbol of ''-'' and i tried with the following query as

update SampleTable1 set Test1='-',Test2='-',Test3='-' where Test1 is null and Test2 is null and Test3 is null

执行时，结果为（0行（s）受影响）。

我的查询有什么问题...指导我

While executing,am getting result as "(0 row(s) affected)".
What is the wrong in my query...guide me

推荐答案

您的表只包含整数字段，您在Update语句中输入字符值。这是绝对错误的。



将表格列数据类型更改为 varchar 或 nvarchar 然后只有你可以在列中输入 - 值。



谢谢

Hi,

Your table contains only integer fields and you are entering the character value in the Update statement. This is absolutely wrong.

Change your table column datatypes to varchar or nvarchar then only you can enter the "-" value in your columns.

Thanks

试试：

Try:

UPDATE P1
SET 
  P1.Test1= CASE WHEN P1.Test1 IS NULL THEN '-' ELSE P1.Test1 END 
, P1.Test2= CASE WHEN P1.Test2 IS NULL THEN '-' ELSE P1.Test2 END 
, P1.Test3= CASE WHEN P1.Test3 IS NULL THEN '-' ELSE P1.Test3 END 
 
FROM SampleTable1 AS P1
WHERE P1.Test1 IS NULL OR P1.Test2 IS NULL OR P1.Test3 IS NULL

这篇关于如何使用单个查询更新单个表的多个列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！