• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Pritam Chatterjee
Ranch Hand
Posts: 38
Java Netbeans IDE Oracle
  • 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.
 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • 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
33
Linux Oracle Postgres Database Python Scala
  • 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
Java Netbeans IDE Oracle
  • 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?
 
Ron McLeod
Bartender
Pie
Posts: 1049
65
Android Eclipse IDE Java Linux MySQL Database Redhat
  • 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
Java Netbeans IDE Oracle
  • 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
33
Linux Oracle Postgres Database Python Scala
  • 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.
  •  
    • Post Reply
    • Bookmark Topic Watch Topic
    • New Topic