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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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'