Altering and Updating SQL tables

Now there is a full database but you want to update some old rows. We will also use a WHERE operator to make sure we only do it to one row.

UPDATE table SET column_name = ‘Updated data’ WHERE id=1;

To delete a row were need to say what table and give it a WHERE operator to make sure it is only the one row we are looking to delete.

DELETE FROM table WHERE id=1;

When deleting rows you can also use a LIMIT to make sure you are deleting only the ones you want to and not anything extras.

DELETE FROM table WHERE id=1 LIMIT 1;

We can also add new columns to already existing tables. You might be thinking why don’t we just add it to when we created the table. The problem with that is we might have created the table months ago and if we did it that way we might lose all the data that is already in it. The ALTER operation will add new columns to existing tables and any rows that came before the added column will have a NULL value unless other wise stated.

ALTER table ADD new_column text;

Using transactions when altering SQL tables. There are times when we might want to add multiple commands but we might want to only have one command happen if the first command happens. The way to make sure that the command will only go through only if the first command happens is by using a TRANSACTION

BEGIN TRANSACTION;

INSERT INTO column1 VALUES (‘updated data’)

UPDATE table SET activity =’updated table1' WHERE column1=’updated data’

COMMIT;