CODEWITHSUNDEEP
c#mysql.netsql
YosiFZ
YosiFZ

Reputation: 7890

Move rows from table to table

I try to move rows that expired from one table to another with this:

MySqlConnection connect = new MySqlConnection(connectionStringMySql);
MySqlCommand cmd = new MySqlCommand();

cmd.Connection = connect;
cmd.Connection.Open();

string commandLine = @"INSERT INTO history SELECT clientid,userid,startdate,
                       enddate,first,city,imgurl,phone,type,seen 
                       FROM events WHERE startdate<now();";

cmd.CommandText = commandLine;

cmd.ExecuteNonQuery();
cmd.Connection.Close();

The table are exactly the same (in each table I have id column with primary key, with auto increment) and when I run it i get this exception:

Column count doesn't match value count at row 1

Any idea why it crash?

Upvotes: 2

Views: 616

Answers (2)

John Woo
John Woo

Reputation: 263683

The reason why you get the error is because the number of column on the table doesn't match on the number of values being inserted. This is usual when you have an auto-incrementing column on the table you are inserting.

In order to fix the problem, you need to specify the column name where the values will be inserted. Example,

INSERT INTO history (col1, col2,....) // << specify column names here
SELECT clientid, userid, startdate, enddate, first, 
       city, imgurl, phone, type, seen 
FROM   events 
WHERE  startdate < now()

I'm not pretty sure of the column names of table history that's why you need to change the column names to your valid names on your table.

For instance you have an auto-incrementing column on table history and you want to leave the query as is, you can pass NULL on the SELECT statement just to match the total number of columns to the total number of values. Example,

INSERT INTO history 
SELECT NULL, clientid, userid, startdate, enddate, first, 
       city, imgurl, phone, type, seen 
FROM   events 
WHERE  startdate < now()

Keep in mind that in the case you won't specify the column names, the order of the values does matter.

Upvotes: 1

Pouki
Pouki

Reputation: 1664

Try : 

string commandLine = @"INSERT INTO history (clientid,userid,startdate,enddate,first,city,imgurl,phone,type,seen) SELECT clientid,userid,startdate,enddate,first,city,imgurl,phone,type,seen FROM events WHERE startdate<now();";

Upvotes: 0

Related Questions