Error 1136 (21s01):

I have been trying to update a table using the information in another

Here’s the query I am trying to use (I am not sure it’s correct)

INSERT INTO table1 SELECT * FROM table2 INNER JOIN table1 using (id);

when i try to run it, i get the error

ERROR 1136 (21S01): Column count doesn’t match value at row 1

The two tables are setup using the exact same create statement, except for the name of course, so I am not sure if this is a table error or a syntax error

CREATE TABLE table1 (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
fname TEXT NOT NULL,
lname TEXT NOT NULL,
city TEXT,
state TEXT,
zip INT,
eaddress TEXT NOT NULL,
reason TEXT NOT NULL,
message LONGTEXT NOT NULL
) DEFAULT CHARACTER SET utf8;

Thanks in advance for the help…

Sorry you are correct… I did want to insert… I was multi tasking and momentary confusion set in.

Let me try that out and I’ll post back with any further issues…

As far as pebcak goes… he’s probably right…:blush:

um, no, it’s the other way round :slight_smile:

he’s selecting from table2 joined to table1, and inserting the results into table1, and that’s a non-starter because there are 2N columns in the dreaded, evil “select star”

your suggestion is to select N-1 columns (i.e. without the id) but the real question is what the join is supposed to be doing, and which of the 2N columns he really needs

I took out the ‘*’ and inserted the names of the name of teh fields and it works after a couple of tweaks. Thanks again…

Updated? Didn’t you want to insert?

Second (and probably more importantly)… pebcak??? what’s that?

http://en.wiktionary.org/wiki/PEBCAK

Just googling for it gave the answer :slight_smile:

What you need to do is get rid of the * in the SELECT, and instead specify each single column from table2 you want to insert in table1. Just make sure you specify the right number of values :slight_smile:

Thank you all for the information… its greatly appreciated.

Two questions…

First… If I don’t include the the ID then how is it going to know which record needs to be updated.

Second (and probably more importantly)… pebcak??? what’s that?

how do you know?

suppose table1 has 37 rows, and table2 has 9 rows, of which 5 match a row in table1, and he wants to clone those 5 rows of table1 as new rows for table1?

i have learned (through bitter experience) that when people post questions about “table1” and “table2” (i.e. dumbed down or obfuscated), you cannot make any assumptions

speed reading caught me out there.

no join needed and only the cols required should be selected, I think.


insert into table1
(col2,col3)
select col2, col3
from table2

yup, your’e right. I made the assumption that the OP was trying to copy records from the one table to the other without anything more convoluted

pebcak is spreading :frowning:

bazz

it’s a pebcak error :smiley: :smiley:

if table1 and table2 each contain N columns, then the SELECT portion of your query (which uses the dreaded, evil “select star”) has 2N columns, which is clearly a case of “column count doesn’t match”

:D. I had to look that one up rudy.

you are selecting all cols from one table (including the auto_increment id) and then trying to insert all those cols into the other table. Problem is, the second table is making its own auto_increment so the one from the first table is superfluous. select the cols you need from table 1 and insert them into table two. forget about selecting the id.

bazz