Posts Tagged ‘update’

Yes, you can update views in MySQL! A lot of people seem to be under the impression that you are unable to, but this simply isn’t the case. There are a lot of restrictive rules, which can be found in the MySQL manual, but as a rule of thumb, you can update them, but only one table.

For example, lets say you have a view which links table A and table B.

You can run an update on this view, but only if you are only updating the columns from one table. So you could run a query that updates a number of columns from table A, or you could run a query which updates columns from table B – but you can’t run a query which updated columns in both table A and table B.

If you need to accomplish this, you need to use a join instead.

Hello everyone! I just wanted to post a quick update on what the status of the site was now.

We first acquired Hardware Tutorials back in 2005, but since then there hasn’t been much content added to it – and indeed the content that was posted back then wasn’t exactly amazing quality. But for prosperity sake, we have kept it.

Never the less, it has always been an ambition to develop the site further and one thing I constantly find myself doing at work is googling for solutions to problems, usually when I’m trying to install something, usually on CentOS. Though that isn’t to say I don’t have my fair share of problems dealing with OS X, Windows 7 and Fedora too.

Rather than just leaving the solutions I eventually find for these problems, Hardware Tutorials seems a reasonably appropriate place to post such solutions – a lot of it isn’t strictly hardware, but as I said in my opening post six years ago, we would almost certainly be straying from that criteria anyway.

So please enjoy the new and updated Hardware Tutorials. Hopefully, you will be seeing a lot more regularly updated content from now on.

The SQL update command allows you to make specific changes to one or more rows. You could in theory do this by deleting the old one and inserting a new one but this means you have to deal with all the data and can only do one row of data. Using UPDATE solves both of these problems.

Basic syntax

As with everything there is a basic command structure to allow you to sort out what you want and run the various different parts of the commands. The syntax for using UPDATE is as follows:

UPDATE table SET field1 = 'value1', field2 = somenumber WHERE field3 = 'value3'

There are three parts to this. The first is the table to update. The second is the values which you are setting. The third is the validation of which rows in the table should have the update run.

Sample table

To make this easier to do I am going to create a sample table of data from which we can work from. The table is going to be called friends and will have several bits of data in it.

Name	email	age	favourite colour
Jim	jim@mail.com	24	blue
Alex	alex@operamail.com	23	black
Josh	happy@killer.com	30	grey
Mike	mikebob@avril.com	27	blue

Ok first lets say that Josh tells me that his email has changed from happy@killer.com to josh@wiggam.com. So we need to run an update command to change the email address from the old one to the new one.

UPDATE friends SET email = 'josh@wiggam.com' WHERE name = 'Josh'

In this code, all rows where the name is Josh, will have the current email changed to match josh@wiggam.com. This would be a problem if I had two Josh’s but I don’t so only one is updated. If I did have two Josh’s I could change the statement to say:

WHERE email = 'happy@killer.com'

Or I could also try

WHERE name = 'Josh' AND age = 30

Updating multiple values

Next let’s pretend that it’s Alex’s birthday – he turns 24 and decides that his favourite colour is now green. So we need to update two values in the table at once. Luckily this is pretty simple to do.

UPDATE friends SET age = 24, favourite colour = 'green' WHERE name = 'Alex'

First thing I must stress – you should never have a column name with a space it – don’t do it. But seen as this is just an example it’s not a problem. If you really needed a name like that then using FAVOURITE_COLOUR or a dash, etc to space them out.

In the above update statement both age and favourite colour are updated in every row where the name is Alex. Also there are no hyphens around age as it’s a number and so does not require them. You will get an error if you try to put them round a number when using a number column, as you will do for not using them in a text column.

Updating multiple rows

Finally I am going to look at how to update two records at a time. Though we have really already covered it so this is more of a confirmation to make sure you have it right. Take a look at this code:

UPDATE friends SET age = 60 WHERE favourite colour = 'blue'

I decide that everyone who likes blue is an old foogy so I am going to update everyone’s age to 60 who said their favourite colour was blue. As usual the script looks for everyone who is ok by the WHERE validation. This time it finds both Jim and Mike and changes both their ages to 60.

Conclusion

The UPDATE command is fairly simple – you just specify the table to update, the values to set and the conditions a row must meet for the update to be run on that row. One final thought though – take another look at Mike’s email address and try and guess what music I was listening to when I wrote that one :).

Yep, it was of course Feeder ;).