[RESOLVED] Transferring data from one table to another using mysql query

computerzworld

Hello Experts,
I am having two tables and I want to move the data from one table to another using mysql query. All the fields of first table(from which I want to move data) are in second table and two extra fields are there. I want to move the data of first table to second table when certain condition is satisfied. So, I have written query like,

insert into table2('field1','field2','field3','field4','field5') select 'field1','field2','field3' from table1 where 'field1'<1,'field4 value','field5 value';

But it gave me error when I ran it.. What exactly I want to do is to insert all the values of first table into second one and two extra fields that are present in the second table. But I am not able to insert. Please help me if anybody have solution of this. Thanks in advance.

jazz_snob

what was the actualy query string and what was the exact error message?

computerzworld

its giving me error near comparision part of select query.... and if i remove field4 value and field5 value then it gives me error of column count mismatch...

jazz_snob

Try using fully qualified column names, in other words table1.field1 and table2.field1.

And yes you need 5 values to match your insert statement so pulling out stuff from the select part will give you errors unless you adjust your insert portion to match.

computerzworld

yup exactly that's what I am asking... Actually I am having 5 columns in seond table and I want 3 values from first table and 2 values are inserted directly in the second table using insert query.. How can I actually insert values like this?

jazz_snob

Three values are to be selected from the first table. I don't understand where the other two values come from. And this looks a bit odd:

insert into table2('field1','field2','field3','field4','field5') select 'field1','field2','field3' from table1 where 'field1'<1,'field4 value','field5 value';

I'd expect the where clause to not have anything else after it:

where t1.field1<1;

You might have to use a temporary table if you are trying to read values from both table 1 and table 2 and insert them into table 2. Or make a new table all together.

computerzworld

actually other two values are current time and one static string.... and that i want to insert in second table....

jazz_snob

insert into table2(field1,field2,field3,field4,field5) select table1.field1, table1.field2, table1.field3, now(), 'static string' from table1 where table1.field1<1;

Note this assumes your date column in table2 is a "date" column and not a unix timestamp (ie not an "int" column)

computerzworld

hi i tried exactly what you have said but it gaves me error.. even I tried with seperating out only select query like this

select table1.field1, table1.field2, table1.field3, now(), 'static string' from table1 where table1.field1<1;

then also it gave me error. Please help me.

jazz_snob

Hmmm at that point I'd have to see the actual query string and error statement to be helpful.

computerzworld

problem solved....🙂 thanks.... actually there was a problem in my query... the format that you have said was perfectly ok.... and i have rewritten the query and the problem solved🙂 thanks again...