How can i add values to one column in a table in mysql?

Question

Let us first create a table −

Table of Contents Show

Introduction to a MySQL INSERT Statement
MySQL INSERT INTO – General Syntax
MySQL INSERT Examples
MySQL INSERT command used to insert specified values
MySQL INSERT command used to insert values to all columns
MySQL INSERT command used to insert the default value
MySQL INSERT used to insert a specific value
MySQL INSERT used to insert data from another table
MySQL INSERT – ignore errors when inserting data
Creating the INSERT INTO Query with dbForge Studio for MySQL
Conclusion
How can I add values to one column in a table in SQL?
How do I add values to just one column?
How can I add multiple values in one column in MySQL?
How can I add multiple values in one column in SQL?

mysql> create table DemoTable(
   Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   Name varchar(100)
);
Query OK, 0 rows affected (0.47 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable(Name) values('John');
Query OK, 1 row affected (0.10 sec)
mysql> insert into DemoTable(Name) values('Bob');
Query OK, 1 row affected (0.10 sec)
mysql> insert into DemoTable(Name) values('Adam');
Query OK, 1 row affected (0.28 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable;

This will produce the following output −

+----+------+
| Id | Name |
+----+------+
| 1 | John  |
| 2 | Bob   |
| 3 | Adam  |
+----+------+
3 rows in set (0.00 sec)

Here is the query to add column −

mysql> alter table DemoTable add column Gender ENUM('MALE','FEMALE');
Query OK, 0 rows affected (0.55 sec)
Records :0 Duplicates : 0 Warnings : 0

Following is the query to insert data into a new column of an already existing table −

mysql> update DemoTable set Gender='FEMALE' where Id=2;
Query OK, 1 row affected (0.13 sec)
Rows matched − 1 Changed − 1 Warnings − 0

Let us check the table records once again −

mysql> select *from DemoTable;

This will produce the following output −

+----+------+--------+
| Id | Name | Gender |
+----+------+--------+
|  1 | John | NULL   |
|  2 | Bob  | FEMALE |
|  3 | Adam | NULL   |
+----+------+--------+
3 rows in set (0.00 sec)

Updated on 03-Sep-2019 13:38:51

Related Questions & Answers
Adding new enum column to an existing MySQL table?
How to create and fill a new column in an already created MySQL table?
Inserting random numbers into a table in MySQL?
How to rename a column in an existing MySQL table?
Adding a new NOT NULL column to an existing table with records
Which PHP function is used to insert data into an existing MySQL table?
Add a new column and index to an existing table with ALTER in a single MySQL query?
How to add a new column to an existing table using JDBC API?
Inserting records into a MySQL table using PreparedStatement in Java?
How to add a column using MySQL SELECT in an already created table?
How can we insert data into an existing MySQL table by using PHP script?
How can I drop an existing column from a MySQL table?
Can we skip column when inserting into MySQL?
How to add a new column at the front of an existing R data frame?
Write an JDBC example for inserting value for Clob data type into a table?

The article covers the basic syntax of the MySQL INSERT statements and explains how to use the INSERT command in the MySQL table using dbForge Studio for MySQL.

Introduction to a MySQL INSERT Statement

The INSERT statement is used to add data to a table. The INSERT INTO command inserts one or multiple rows into a MySQL table. Depending on the number of rows you want to add, the syntax slightly differs.

MySQL INSERT INTO – General Syntax

When inserting a single row into the MySQL table, the syntax is as follows:

INSERT INTO table_name(column_1,column_2,column_3) 
VALUES (value_1,value_2,value_3);

In the INSERT INTO query, you should specify the following information:

table_name: A MySQL table to which you want to add a new row.
(column_1,column_2,column_3): A list of columns the new row will contain. The columns are separated by a comma in round brackets.
(value_1,value_2,value_3): A list of values separated by a comma in round brackets that will be used for each corresponding column.

The number of columns and values should match; otherwise, it fails.

When inserting multiple rows into a MySQL table, the syntax is as follows:

INSERT INTO table_name (column_1, column_2, column_3, ...) 
VALUES 
      (value_list_1), 
      (value_list_2), 
      ...
      (value_list_n);

Here you should enter the following information:

table_name: A table into which you want to insert data.
column_1, column_2, column_3: A list of columns to store data. The columns are separated by a comma in round brackets.
value_list_1/2/.../n: A list of values to be inserted in one row. The values are separated by a comma in round brackets. The number of values in each list should match the number of columns specified in (column_1, column_2, column_3, ...).

In addition, rows are separated by commas in the VALUES clause.

MySQL INSERT Examples

Let’s explore some examples of using a MySQL INSERT statement when performing data-related tasks.

To start with, create three tables: Customers, Products01, and Smartphones by executing the following script:

CREATE TABLE Customers
(
  ID int,
  Age int,
  FirstName varchar(20),
  LastName varchar(20)
);

-- Creating the Products table

CREATE TABLE Products01
(
  ID int,
  ProductName varchar(30) NOT NULL,
  Manufacturer varchar(20) NOT NULL,
  Price decimal NOT NULL,
  ProductCount int DEFAULT 0
);

CREATE TABLE Smartphones
(
  ID int,
  ProductName varchar(30) NOT NULL,
  Manufacturer varchar(20) NOT NULL,
  Price decimal NOT NULL,
  ProductCount int DEFAULT 0
);

MySQL INSERT command used to insert specified values

This command can be used when it is necessary to add values and columns for the specified rows. For example, add data only for the FirstName and LastName columns of the Customers table.

INSERT INTO Customers(ID, FirstName, LastName)
VALUES ('1', 'User', 'Test');

The command inserts values in the FirstName and LastName columns of the Customers MySQL table. Since the Age column uses Null as a default value, MySQL inserts Null into the Age column if you do not specify explicitly its value in the INSERT statement.

MySQL INSERT command used to insert values to all columns

This command can be used in order to add values to all columns. For example, add values to the Product01 table. Keep in mind that the order of values should match the order of columns in the table.

INSERT INTO Products01
VALUES ('01', 'iPhoneX', 'Apple', 35000, 3);

The statement inserts the specified values into all columns of the Product01 table.

MySQL INSERT command used to insert the default value

This command can be used when the default value is specified for the column. For example, the default value for the ProductCount column of the Products01 table equals 0. To insert the default value, execute either of the following queries:

INSERT INTO Products01(ProductName, Manufacturer, Price, ProductCount)
VALUES ('Nokia 9', 'HD Global', '41000', DEFAULT);

INSERT INTO Products01 (ProductName, Manufacturer, Price, ProductCount)
VALUES ('Nokia 9', 'HD Global', '41000', NULL);

The statement inserts the default value for the ProductCount column.

MySQL INSERT used to insert a specific value

To insert values into the columns, we can use the SET clause instead of the VALUES clause.

INSERT INTO Customers SET ID=2, FirstName='User2';

In the output, the statement inserts values into the columns based on the explicitly specified values in the SET clause.

MySQL INSERT used to insert data from another table

Using the SELECT clause in the MySQL INSERT INTO statement allows inserting the rows generated by the SELECT statement into the target MySQL table.

INSERT INTO Products01 SELECT * FROM Smartphones;

In this case, the INSERT INTO statement copies the rows you retrieved with the SELECT statement from the Smartphones table and inserts them into the Products01 table.

If you want to retrieve specific rows based on the condition, apply filtering options in the WHERE clause. Keep in mind that the structure of both tables should match; otherwise, it won’t work.

INSERT INTO Products01 SELECT * FROM Smartphones WHERE Price=34000;

This command adds to the Products01 table values from the Smartphones table where the price equals 34000.

You can also copy and insert specified columns from another table. However, in this case, make sure that there is no record with the specified name in the target table; otherwise, it fails, and the error occurs.

MySQL INSERT – ignore errors when inserting data

In case you face errors or any unexpected behavior while executing the insertion of rows into the MySQL table, the statement execution is interrupted. Rows with valid and invalid values are not added to the table. For example, execute the following query:

INSERT INTO Products01
VALUES ('01', 'iPhoneX', 'Apple', 35000, 3);

This statement returns the error because the record with this ID already exists. Now, modify the statement with IGNORE. It allows you to insert rows with valid values but overpass the rows with invalid values.

INSERT IGNORE INTO Products01
VALUES ('01', 'iPhoneX', 'Apple', 35000, 3);

As a result, the script was executed without errors. MySQL will notify you that rows were added to the table.

Creating the INSERT INTO Query with dbForge Studio for MySQL

All the data manipulations, including the Create, Select, Insert, Update, Delete, or Drop operations can be easily performed with dbForge Studio for MySQL. The tool offers a rich set of advanced capabilities and features to boost your productivity and save your time when developing, managing, and deploying MySQL database in the easy-to-use interface.

Assume we need to add a new customer to the database. In dbForge Studio for MySQL, we can use the Generate Script As feature to insert one or multiple rows in the MySQL table.

To insert information about customers, do the following:

1. In Database Explorer, right-click the required table and select Generate Script As > INSERT > To New SQL Window. The SQL code editor opens containing the automatically generated script.

2. In the SQL code editor, insert data for the specified columns (first_name, last_name, and email) into the VALUES clause.

3. On the standard toolbar, click Execute.The statement adds the First Name, Last Name, and email of the new customer to the bottom line of the grid.

Conclusion

In the article, we have reviewed a MySQL INSERT statement and examples, when the command can be used, and provided a particular example of how to work with the MySQL INSERT queries in dbForge Studio for MySQL.

Download a free 30-day trial version of dbForge Studio for MySQL to evaluate all the useful features the tool provides.

Author
Recent Posts

mysql insert statement, mysql tools

How can I add values to one column in a table in SQL?

INSERT INTO Syntax Specify both the column names and the values to be inserted: INSERT INTO table_name (column1, column2, column3, ...)

How do I add values to just one column?

To insert values into specific columns, you first have to specify which columns you want to populate. The query would look like this: INSERT INTO your_table_name (your_column_name) VALUES (the_value);

How can I add multiple values in one column in MySQL?

MySQL INSERT multiple rows statement In this syntax: First, specify the name of table that you want to insert after the INSERT INTO keywords. Second, specify a comma-separated column list inside parentheses after the table name. Third, specify a comma-separated list of row data in the VALUES clause.

How can I add multiple values in one column in SQL?

The INSERT statement also allows you to insert multiple rows into a table using a single statement as the following: INSERT INTO table_name(column1,column2…) VALUES (value1,value2,…), (value1,value2,…), … In this form, you need to provide multiple lists of values, each list is separated by a comma.

kode mysql CREATE TABLE MySQL