Changing Table Information

Our working knowledge of the SELECT command comes into play with other commands as well. For instance, the INSERT command we saw earlier can also take a SELECT clause. This allows us to insert query information into an existing table. If our software department were to merge with IT, we could add their machines to the itmachines table:

USE sysadm INSERT itmachines SELECT name,ipaddr FROM hosts WHERE dept = 'Software'

If we want to change any of the rows in our table, we can use the UPDATE command. For example, if all of the departments in the company moved into a single facility called Central, we can change the name of the building in all rows like so:

USE sysadm UPDATE hosts SET bldg = 'Central'

It's more likely that we'll need to change only certain rows in a table. For that task, we use the handy WHERE clause we saw when discussing the SELECT operator:

USE sysadm UPDATE hosts SET dept = 'Development' WHERE dept = 'Software'

That changed the name of the Software department to Development. This moves the machine called bendir to our Main building:

USE sysadm UPDATE hosts SET bldg = 'Main' WHERE name = 'bendir'

If we wanted to remove a row or set of rows from a table instead of updating them, we can use the DELETE command:

USE sysadm DELETE hosts WHERE bldg = 'East'

There's no way to undo a straight DELETE operation, so be careful.