SQL Tutorial: Easily Update Rows Based on Complex Joins

Comment 1 Standard

There are a lot of times I’d like to update hundreds of rows in a database based on a complex set of conditions. In doing a traditional update SQL statement there is no way to pull in joins and compare the values. My little cheat for this is to run a select statement that generates all the appropriate update statements. Then all you have to do is run the resulting SQL string.

For example: Lets say you have a car racing game. You added a new garage feature where members can sort their cars into the appropriate garage. By default you gave each member their own garage when you created the new feature. Now you want to update each car belonging to every member so that it is placed inside the appropriate garage. You could build something like the following.

select ("update cars set garage='" + G.id + "' where car_id='" +  C.id + "';") as SQL from garage G
inner join cars C on C.owner = G.owner

This would give you results along the lines of:

update cars set garage='2' where car_id='3421';
update cars set garage='2341' where car_id='133212';
update cars set garage='4452'' where car_id='98321';

Advertisements

MySQL FIX: connect to server at ‘localhost’ failed error: ‘Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’

Comments 2 Standard

In short the error means that mysql isn’t running. So the next reason is, why?

Recently I upgraded from php4 to php5 on one of my servers. Ever since then any time mysql does a restart I was getting this connection error. When the job went to restart it was failing and I’d be stuck  going in and starting it myself. Sooo, the way to fix it is to open your etc/my.conf file and comment out basedir=/var/lib.

Source: http://forums.theplanet.com/lofiversion/index.php/t90492.html