how to copy a data from a table to anotehr table?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
omegahawk
Forum Newbie
Posts: 4
Joined: Sat Apr 05, 2003 9:17 pm

how to copy a data from a table to anotehr table?

Post by omegahawk »

well this is if im not mistaken to copy the whole data in a table to another table

INSERT into destination_table SELECT * from source_table;

but how about if i want to copy a data from table a to table b?

let say that we know the username and we want to copy his real name to another table.....

well i got this one but it wont work.

$sql = "UPDATE details,mn SET details.name=mn.name, where details.idnum=mn.idnum";
$result=mysql_query($sql) or die("Died inserting login info into db. Error returned if any: ".mysql_error());


it gives out an error

Died inserting login info into db. Error returned if any: You have an error in your SQL syntax near 'mn SET details.name=mn.name, where details.matric=mn.matric' at line 1


anyone?..
Coco
Forum Contributor
Posts: 339
Joined: Sat Sep 07, 2002 5:28 am
Location: Leeds, UK
Contact:

Post by Coco »

can you have a select in an insert query?
in mysql 3?
:?
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Coco wrote:can you have a select in an insert query?
in mysql 3?
:?
Yes you can:
http://www.mysql.com/doc/en/INSERT_SELECT.html

Mac
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Re: how to copy a data from a table to anotehr table?

Post by twigletmac »

omegahawk wrote:well i got this one but it wont work.

$sql = "UPDATE details,mn SET details.name=mn.name, where details.idnum=mn.idnum";
$result=mysql_query($sql) or die("Died inserting login info into db. Error returned if any: ".mysql_error());


it gives out an error

Died inserting login info into db. Error returned if any: You have an error in your SQL syntax near 'mn SET details.name=mn.name, where details.matric=mn.matric' at line 1
It probably isn't working because you're most likely using MySQL version 3 and that syntax is only supported by MySQL version 4.0.4 or above:
http://www.mysql.com/doc/en/UPDATE.html

You'd have to find another - most likely less simple - solution. Although it's normally not good practise to have identical information stored in different tables.

Mac
Post Reply