Copying a row from table1 to table2 which has 2 extra fields

dougmcc1

I have 2 tables. One of them has 2 more fields than the other. What I want to do is move the entire contents of the smaller table into the bigger table. Then I want to fill in the other 2 fields after the data is moved. What I'm doing isn't working:

mysql_query("INSERT INTO table2 SELECT * FROM table1 WHERE id='$number'");
mysql_query("INSERT INTO table2 (variable1,variable2) VALUES ('$variable1','$variable2') WHERE id='$number'");

None of the rows get filled in at all. But when I delete the 2 extra fields from table2 and do this, it works:

mysql_query("INSERT INTO table2 SELECT * FROM table1 WHERE id='$number'");

So I don't see why a combination of my two queries are causing a problem. What am I doing wrong?

LordShryku

You need to specify what fields you are inserting to.

INSERT INTO table1 (field1, field2, field3) SELECT * FROM table2

dougmcc1

Thanks for your reply. I am specifying my fields with this statement:

mysql_query("INSERT INTO table2 (variable1,variable2) VALUES ('$variable1','$variable2') WHERE id='$number'");

Do you mean that I have to specify all of them in a single statement?

LordShryku

INSERT INTO table2 (variable1,variable2) VALUES ('$variable1','$variable2') WHERE id='$number'

That should work. The snippet I gave you is for a sub-select INSERT. When you have a SELECT * sort of statement, and are inserting into another table, the column count either needs to match, or you need to specify what columns to fill.

dougmcc1

Ah, ok. I was wondering if that might be why the INSTER INTO SELECT statement was working by itself but not with the other one.

I was hoping to not have to SELECT every single field from table1 before I entered them into table2, especially since they contain the same information. Just seems like a lot of extra code. Oh well.

You helped me the other day too, thanks again for your help.

LordShryku

No problem. If you don't want to select every field, what exactly do you want to do? If you want to do them one at a time, you can add the WHERE satement to your sub-select.

INSERT INTO table1 (field1, field2, field3) 
SELECT * FROM table2 WHERE field1 = 'something'

Don't know how well mySQL will handle this. Think it might depend on ytour version. You may also want to add parentheses around your subquery.