Mastering in SQLite

Changing Data

Before getting into changing data, if you haven’t covered the previous topic- Constraints, then get into it.

To visit the introduction page to see the available topics click here

A number of superheroes are available in our DB, click here to download it.

Changing Data

Here we are gonna discuss about how to modify data in the table.

INSERT

After creating a table, we need to insert data into a table. This can be done by INSERT statement. SQLite provides various forms of the INSERT statements that allow you to insert a single row, multiple rows, and default values into a table.

Syntax

INSERT INTO table (column1,column2 ,..) 
VALUES( value1, value2 ,...);

The following statement add a row into marvelCharacters table

INSERT INTO CharacterList(id,name,weapons,yearCreated,movieName,studio) VALUES(1,”CaptainAmerica”, ”Vibranium shield”,1941,"Captain America,"marvel");

To add multiple rows,

INSERT INTO CharacterList(id,name,weapons,yearCreated,movieName,studio) 
VALUES(2,"BlackPanther", "anti-metal claws",1966,"Black Panther","marvel),
      (3,"Wolverine","daggers",1974,"xMen","marvel"),
      (4,"Ultron","plasma weapons",1968,"theAvengers","marvel");

The third form of the insert statement is INSERT DEFAULT VALUES, which inserts a new row into a table using the default values specified in the column definition or NULL if the default value is not available and the column does not have a NOT NULL constraint.

INSERT INTO CharacterList DEFAULT VALUES; 

UPDATE

To update existing data in a table, we should follow the rules

• First, specify the table where you want to update after the UPDATE clause.
• Second, set new value for each column of the table in the SET clause.
• Third, specify rows to update using a condition in the WHERE clause. The where clause is optional. If you skip it, the update statement will update data in all rows of the table.

Syntax

UPDATE table 
SET column_1 = new_value_1,
    column_2 = new_value_2 
WHERE search_condition;

Update one column,

UPDATE CharactersDetails
SET salary = 150000
WHERE 
    id = 2;

To see the changes, use the select command to view the table.

Update multiple columns,

UPDATE CharacterList
SET weapons = "stormbreaker",
    yearCreated = 1963
WHERE 
     id = 9;

Update all rows,

UPDATE CharacterList
SET name = UPPER(name);

The upper( ) function will change the names to upper case.

DELETE

You have learned how to insert a new row into a table and update existing data of a table. Sometimes, you need to remove rows from a table. In this case, you use SQLite DELETE statement.

The SQLite Delete statement allows you to delete one row, multiple rows, and all rows in a table.The rules to be followed are

• First, specify the name of the table which you want to remove rows after the DELETE FROM keywords.
• Second, add a search condition in the where clause to identify the rows to remove. The where clause is an optional part of the delete statement. If you omit the where clause, the delete statement will delete all rows in the table.

Syntax

DELETE FROM table 
WHERE search_condition;

To delete a single row,

DELETE FROM 
       CharactersDetails
WHERE 
       id = 9;

Suppose if we need to delete the names who have salary < 10000.

DELETE FROM 
       CharactersDetails
WHERE 
       salary < 10000;

This will delete all the rows of the person who has salary less than 10000.

To delete all rows from the table, you just need to omit the where clause statement as

DELETE FROM 
       marvelCharacterDetails;

REPLACE

The idea of the REPLACE statement is that when a unique or primary_key constraint violation occurs, it does the following:

• First, delete the existing row that causes a constraint violation.
• Second, insert a new row.

In the second step, if any constraint violation e.g., NOT NULL constraint occurs, the replace statement will abort the action and roll back the transaction.

Syntax

REPLACE INTO table(column_list) 
VALUES(value_list);

The following REPLACE statement inserts a new row into the marvelCharacters table because Hulk details is not present in the marvelCharacters table.

REPLACE INTO CharactersDetails(id,name, superPower) 
VALUES(14,'Hulk','Hulk smash');

Reach out the next topic - Filtering Data

Sridharan T