Archive for the ‘Databases’ Category

I have just been refactoring a legacy database and needed to update some data in one table from another table in MySQL.

In this case the 2 tables had the same values for the identity colomn.

Syntax:

UPDATE tablename1 t1
SET colomn1 = (SELECT colomn1 from tablename2 t2 WHERE t1.id = t2.id);

For example

If i had tables: ‘products’ and ‘merchandise’ and needed to copy the product title into the merchandise title colomn, I would simply:

UPDATE merchandise m
SET title = (SELECT title from products p where m.id = p.id);

I have just been refactoring a legacy database and needed to copy some data from one table into a new table in MySQL.

Very easy task but very easily forgotten, just thought I would blog about it to help anyone searching for a quick answer…

Syntax:

INSERT INTO tablename (colomn1, colomn2 ...)
SELECT colomn1, colomn2 ...
FROM tablename;

For example

If i had a products table and needed to copy the product title into another table named ‘merchandise’ I would simply:

INSERT INTO merchandise (title)
SELECT title FROM products;

Something I seem to do quite regularly at the moment is resetting the auto increment value for a table in a MySQL database.

Really easy to do but also very easy to forget the syntax:

ALTER TABLE tablename AUTO_INCREMENT = value;

For example
If I had a products table and I created a few new products and then deleted them, to set the auto increment value back to ‘53′ i would simply:

ALTER TABLE products AUTO_INCREMENT = 53;