Error 1136 (21s01):

Databases
1

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…

2

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:

3

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

4

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

5

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:

6

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:

7

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?

8

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

9

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
10

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

11

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”

12

: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