How to update multiple columns in SQL if all columns having same data type and same data?

Question

0.00/5 (No votes)

See more:

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:

SQL

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.

Posted 26-Sep-16 2:27am

Tarun Mittal Delhi

Updated 26-Sep-16 4:26am

Wendelius

v2

Add a Solution

Comments

Maciej Los 26-Sep-16 8:31am

Would you like to add the same value for each column?

Tarun Mittal Delhi 26-Sep-16 8:33am

yes...

Karthik_Mahalingam 26-Sep-16 9:45am

what is the issue?

Tarun Mittal Delhi 26-Sep-16 14:43pm

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.

---
regards,
tarun mittal

Maciej Los 26-Sep-16 16:39pm

Check updated answer ;)

Tarun Mittal Delhi 27-Sep-16 1:19am

thanks for your solution. But, value of all 44 columns also will be different for every ISBN. so, is there any method to write a short query to update all records using define a single column for all 44 columns...???? i hope, you get it what i want to do

Maciej Los 27-Sep-16 1:49am

Think of it. Solution has been provided. All what you need is to change the query to your needs.

Richard Deeming 27-Sep-16 8:37am

A very bad design!

You need three tables:

* Books: (ISBN, [other book details...])
* Countries (CountryCode, [other country details...])
* BookPrices (ISBN, CountryCode, Price)

Your update then becomes:

UPDATE BookPrices
SET Price = @NewPrice
WHERE ISBN = @ISBN;

Tarun Mittal Delhi 28-Sep-16 1:00am

i'm already following this structure.

Richard Deeming 28-Sep-16 8:06am

Not according to your question and your comments! :)

Richard Deeming 26-Sep-16 10:07am

44 columns in one table, all containing the same data?

Seems like somebody needs to learn how to normalize a database.

https://en.wikipedia.org/wiki/Database_normalization[^]
http://www.essentialsql.com/get-ready-to-learn-sql-database-normalization-explained-in-simple-english/[^]

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2016-09-26T02:38:00

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:

SQL
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:

SQL

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'

How to update multiple columns in SQL if all columns having same data type and same data?

1 solution

Solution 1

Add your solution here

Preview 0

How to update multiple columns in SQL if all columns having same data type and same data?

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us