Arquiva-me

I ran into a problem where i had to update two values on a table. One was fixed, but the other depended on a value stored on a second table. This is how its done.

TableA

id_cli   test_cut

1          15

2          56

TableB

id    num_of        job    test_cut    id_client

1     20090464    34      0               2

2     20081012    200    0               1

I needed to update the value of the fields “job” and “test_cut” on Table B, but the value i wanted to put on “test_cut” needs to come from Table A, linked by field “id_cli”. Confusing?

update TableB Tb,TableA Ta set Tb.job_meio=’550′,Tb.test_cut=Ta.test_cut where Tb.num_of=’20090494′ and Ta.id_cli=Tb.id_cli

This update joins the two tables by the field id_cli, fetches the test_cut on TableA and updates fields test_cut and job_meio on TableB

This will turn TableB into:

id    num_of        job      test_cut    id_client

1     20090464    550 56 2

2     20081012    200     0               1

Found this somewhere on google.

If we need to compare two tables, this query will list the missing records on one of them (on table2):

SELECT t1.id
FROM table1 t1 LEFT JOIN table2 t2 ON t1.id = t2.id
WHERE t2.id IS NULL

Then you do it the other way around to check the mising records on the other table (table1).

To check for different values in identical fields:

SELECT t1.id
FROM table1 t1
INNER JOIN table2 t2 ON t1.id = t2.id
WHERE t1.field1 <> t2.field1
OR t1.field2 <> t2.field2
etc…

This is a simple reminder of the mysql command line tool.

In windows, open a command line, navigate to mysql binary folder (if you have it on your PATH, you can skip the navigation part), and use:

To login:

mysql -u #username# -p#password#

(if you dont add the #password#.. it will ask you for one. Also, note that the password doesnt have a space between the switch)

To show all the databases:

mysql> show databases;

To switch a database:

mysql> use dbname;

To list the tables in the selected db:

mysql> show tables;

To see database’s field formats:

mysql> describe table_name;

To show columns and column information of table_name:

mysql> show columns from table_name;

All mysql commands apply:

mysql> select * from table_name where field_name="blahblah";

NOTE - Always end the command with ;

MYSQL Dump/Restore

On the binary folder, theres another command - mysqldump - that makes, well, dumps.

To make a dump of all the databases on the server:

mysqldump -u #username# -p#password# -A > dumpfile.sql

Dump only certain databases:

mysqldump -u #username# -p#password# db_1 db_2 db_3 > dumpfile.sql

Restore a database dump:

mysql --verbose -user=#username# --password=#password# db_1 < dumpfile.sql

NOTE - note the difference in the mysqdump and mysql when adding the switch for username and password, you can use either “-u #username#” or “–username=#username#”.

Command line help:

General help:

mysql> help

See the help contents:

mysql> help contents

To see help on a specific command/category type:

mysql> help _command_or_category_

Some examples:

mysql> help select
mysql> help administration
mysql> help me because i dont know anything about mysql and want to learn everything in 3 days (ok, doesnt work, but you can still try it for the error message)

Links to resources:

MYSQL commands

mysqldump

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.