Arquiva-me

If we want to make an update to a table, but have to condition that update with values from a second one, we do something like this:

TABLE_1
id_t1 name fee
1 john 100
2 rita 100
3 peter 100

TABLE_2
id_t2 id_t1 type
1 1 single
2 2 single
3 3 married

If we want to update the field “fee” from TABLE_1, for example, to make single people pay more than married people (i know, stupid example, but bear with me), we can do this:

update TABLE_1 t1,TABLE_2 t2 set t1.fee=200 where t1.id_t1=t2.id_t1 and t2.type='single'


this is going to update all the rows in TABLE_1 that belong to “single” people, resulting in:

TABLE_1
id_t1 name fee
1 john 200
2 rita 200
3 peter 100

The update is an inner join with the comma operator.

Heres a link to the MySQL manual page on the update command as a reference. Has some nice examples:

http://dev.mysql.com/doc/refman/5.0/en/update.html

Its simple but i thought of leaving it here if i forget (which is usually the case) how to do it again.

If you want to insert the records from tableA into tableB, you do something like this:

INSERT INTO tableA SELECT * FROM tableB

You have to make sure all fields are the same on both tables or you will get an error.

You can list the fields so you wont go wrong:

INSERT INTO tableA(name,address) SELECT name,address FROM tableB

And you can even prevent the key field from being the same:

INSERT INTO tableA SELECT * FROM tableB ON DUPLICATE KEY UPDATE tableB.state =”changed”

Like i mentioned, simple.

This site will contain an archive of all the junk, problems, code snippets, tricks, hacks, solutions and garbage i want to keep to later use, reuse, alter, etc.

Hopefully it will be of help to someone else.

In portuguese:

Este site conterá um arquivo de todas as tralhas, problemas, soluções e lixo que eu vou querendo arquivar para mais tarde recordar.

Talvez ajude mais alguém.