如果所有列具有相同的数据类型和相同的数据，如何更新SQL中的多个列？ [英] How to update multiple columns in SQL if all columns having same data type and same data?

问题描述

亲爱的专家，



我的桌子有45列，包括主键。并且，44列具有相同的数据类型并且具有相同的数据（例如：数据类型：int，数据：所有44列中的50）。并且，在此表中没有。记录超过3000.所以，我想更新所有记录。

如何使用简短查询更新记录？



我尝试过：

 更新 table_name  set  col_name1 = 50，col_name2 = 50，col_name3 = 50，col_name4 = 50，col_name5 = 50，... upto col_name44 = 50  where  ISBN = '  97890000000' 

注意：ISBN的值将在下一条记录中更改。

解决方案

如果要更新所有记录，只需删除 WHERE 声明;）





< blockquote class =quote>

Tarun Mittal Delhi [ ^ ]写道：

您好，



我有一张与价格相关的表格的书。详细信息：在此表中，为44个拉丁美洲国家定义了44列，主列为ISBN。 ISBN对于每个记录/书籍都是唯一的。现在，我想更新所有44个国家的价格。在这种情况下，我将不得不写这样的查询：

 更新 CountryPrice 设置 MX = 84，GT = 84，SV = 84，HN = 84，NI = 84，CR = 84，PA = 84，CO = 84 ，VE = 84，PY = 84，BO = 84，EC = 84，BR = 84，CL = 84，AR = 84，UY = 84，PE = 84，DO = 84，PR = 84，TT = 84，JM = 84，GY = 84，CU = 84，HT = 84，GP = 84，MQ = 84，BS = 84，BB = 84，LC = 84，CW = 84，AW = 84，VC = 84，GD = 84 ，AG = 84，DM = 84，KY = 84，KN = 84，SX = 84，TC = 84，MF = 84，VG = 84，AI = 84，BL = 84，MS = 84 其中​​ ISBN = '  9789350903186' 

注意：这些列基本上都是国家代码。



我想要更新超过3000条记录上面的例子。请帮助我如何以简短的方式编写所有记录的查询。如果我手动写，那么也会消耗时间。

如果你想为 ISBN <集更新3000条记录/ code>并且您希望根据某些条件从另一个表中获取ISBN代码，您可以使用 SELECT + UPDATE ：





 更新 t1 设置 MX = t2.Field1，GT = t2.Field1，SV = t2.Field1，... 
  FROM  CountryPrice  AS  t1  INNER   JOIN  OtherTable  AS  t2  ON  t1.ISBN = t2.ISBN 
  WHERE  t2.SomeField = '  SomeValue' 




Dear expertisers,

I have a table which has 45 columns including a primary key. And, 44 columns have same data type and also having same data(ex: data type: int, data: 50 in all 44 columns). And, in this table no. of records are more than 3000. So, i want to update all records.
How can i update the records using a short query?

What I have tried:



Update table_name set col_name1=50, col_name2=50, col_name3=50, col_name4=50, col_name5=50, ...upto col_name44=50 where ISBN='97890000000'


Note: Value of ISBN will be change for the next record.

解决方案

If you want to update all records, just remove WHERE statement ;)

[EDIT]

Tarun Mittal Delhi[^] wrote:

Hi,

I have a table which is related for price of the books. Details: In this table, 44 columns are defined for 44 "latin amrican countries" and primary column is "ISBN". "ISBN" is unique for every record/book. Now, i want to update the price for all 44 countries. In this case, i will have to write the query like this:

Update CountryPrice Set MX=84, GT=84, SV=84, HN=84, NI=84, CR=84, PA=84, CO=84, VE=84, PY=84, BO=84, EC=84, BR=84, CL=84, AR=84, UY=84, PE=84, DO=84, PR=84, TT=84, JM=84, GY=84, CU=84, HT=84, GP=84, MQ=84, BS=84, BB=84, LC=84, CW=84, AW=84, VC=84, GD=84, AG=84, DM=84, KY=84, KN=84, SX=84, TC=84, MF=84, VG=84, AI=84, BL=84, MS=84 Where ISBN='9789350903186'

Note: These columns are basically country codes.

I want to update more than 3000 records like this above example. Please help me that how can i write the query for all records in a short way. If i write manually then the time will be consumed too.

If you want to update 3000 records for set of ISBN and you want to get ISBN codes from another table based on some condition, you can use SELECT + UPDATE:

UPDATE t1 Set MX=t2.Field1, GT=t2.Field1, SV=t2.Field1, ...
FROM CountryPrice AS t1 INNER JOIN OtherTable AS t2 ON t1.ISBN = t2.ISBN
WHERE t2.SomeField = 'SomeValue'




这篇关于如果所有列具有相同的数据类型和相同的数据，如何更新SQL中的多个列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！