Changing Table Information (Perl for System Administration)

D.4. 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.