This week's book giveaway is in the OO, Patterns, UML and Refactoring forum.
We're giving away four copies of Five Lines of Code and have Christian Clausen on-line!
See this thread for details.
Win a copy of Five Lines of Code this week in the OO, Patterns, UML and Refactoring forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Bear Bibeault
  • Ron McLeod
  • Jeanne Boyarsky
  • Paul Clapham
Sheriffs:
  • Tim Cooke
  • Liutauras Vilda
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • fred rosenberger
  • salvin francis
Bartenders:
  • Piet Souris
  • Frits Walraven
  • Carey Brown

How to insert multiple values in a new colunm in MySQL table

 
Ranch Hand
Posts: 38
Netbeans IDE Oracle Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have one table with two fields say a,b.
Now I have inserted 5 records in a & b column.
Now after that I want to add one more column(c) in that table using alter command.
Then I want to insert 5 values in that c column so that there will be 5 completely filled records.
Now what code should I write to insert this?Please assist.
Insert command is not working, by update we can update
one value at a time but I want to insert all 5 values at the same time. So please tell me code..
I am using MySQL 5.1 console.
 
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Moved to JDBC forum as this is about MySQL (not MongoDB).
 
chris webster
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Pritam Chatterjee wrote:Then I want to insert 5 values in that c column so that there will be 5 completely filled records.


No, you really want to UPDATE your 5 existing records and set the new column to a given value on each record. Use SQL UPDATE without a WHERE clause if you want to update all the rows to the same value at the same time.

INSERT = create new record
UPDATE = modify existing record
 
Pritam Chatterjee
Ranch Hand
Posts: 38
Netbeans IDE Oracle Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
so how to insert different values. Using update command for 5 times or is there any nested query kind of thing?
 
Marshal
Posts: 3149
466
Android Eclipse IDE TypeScript Redhat MicroProfile Quarkus Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Are the values for column c the same for all 5 records, or different? Did you already ALTER the table to add the new column?
 
Pritam Chatterjee
Ranch Hand
Posts: 38
Netbeans IDE Oracle Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have added the column using alter.
Now I want to insert 5 different values.
 
chris webster
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Pritam Chatterjee wrote:I have added the column using alter.
Now I want to insert 5 different values.


No, you want to UPDATE your existing records and populate the new column with values. INSERT means "create a new record" in a database.

  • Use the WHERE clause in your UPDATE statement to determine which records get updated.
  • If you want every record to have the same value in the new column, the you can do this in a single statement by leaving out the WHERE clause e.g. UPDATE mytable SET c = 'foo'.
  • If you want each record to have a different value, then you need to UPDATE each record separately with the appropriate value e.g. UDPATE mytable SET c = 'foo' WHERE a = 'bar'.
  • However, if the new value can be derived from the existing data in each record, you could do this with a single UPDATE e.g. UPDATE mytable SET c = a+b.
  • If the new value cannot be derived from existing data, then you'll need to write separate UPDATEs with a WHERE clause, but there are only 5 records so it shouldn't be hard.
  •  
    This. Exactly this. This is what my therapist has been talking about. And now with a tiny ad:
    Thread Boost feature
    https://coderanch.com/t/674455/Thread-Boost-feature
      Bookmark Topic Watch Topic
    • New Topic