In a similar fashion, we can modify multiple columns of a particular table using a single ALTER TABLE query with modify clause. INSERT INTO `developers` (`joiningDate`, `developer_id`, `team_id`, `name`, `age`, `position`, `technology`, `salary`, `experience`, `address`) VALUES(DEFAULT,'11','2','Suresh','0','Designer','MySQL','20000','0','') Īnd now, let us retrieve the records of developers table and check the joiningDate column of Suresh by using following query – Let us check the records of the developer’s table after inserting one record – For this, we will make the use of the following ALTER TABLE query with MODIFY clause in the following way – Let us alter the joining date column by setting the default value to “”. Where all the names used have the same as ADD command except name_of_column_to_modify that stands for the name of the existing column that needs to modify. MODIFY name_of_column_to_modify details_of_column The syntax of modifying the column is similar to adding the column which is as follows – We can modify the column by changing its definition and position by using the ALTER TABLE statement with MODIFY command. ALTER TABLE statement with MODIFY command The default value of integer datatype column is 0, varchar i.e string is “” blank and for date is NULL. Let us now, retrieve the records from developers table to view our changes by executing following select query –Īs we can see all the new columns are added at their respective positions that we mentioned and are initialized to its default value depending on their datatype. We can do so, by executing the following ALTER TABLE command – Suppose, we want to add three more columns named joiningDate, age, and address. We can even add multiple columns in single query statement. Note: That the value of the newly added experience column is set to 0 as we have mentioned it in the column definition. After executing the following SELECT command – Let us consider an existing table named developers. The default position where the new column is added is at the last. FIRST represents that the new column will be placed in the beginning while AFTER signifies the position of the new column after name_of_existing_column named column in the table. name_of_existing_column: We can specify the position of the column which we are adding with respect to the existing position of the columns in the table named name_of_table by using the FIRST and AFTER keywords.details_of_column: This helps to specify the details and definition of the new column we are adding that includes the datatype of the column and other details such as NULL or NOT NULL, UNIQUE, etc.
0 Comments
Leave a Reply. |