Joining tables

[karimali831]

I want to join two tables together, consisting of the same columns with different records.

 

INSERT INTO table(ID, col1, col2) values

(1, 'rec', '')

(2, 'rec2', '')

(3, '', '')

(4, '', '')

 

table 2:

 

INSERT INTO table(ID, col1, col2) values

(1, 'rec', '')

(2, 'rec2', '')

(3, '', '')

(4, '', '')

 

I know I will get SQL error, duplicated entry (ID)

is there a way it can insert a non existing ID in the table instead of saying there is a duplicate?

[Psycho]

Your request isn't making sense. You say you want to JOIN tables but then you are asking if you can INSERT. Based upon your examples you are trying to run the same insert statement twice.

 

...is there a way it can insert a non existing ID in the table instead of saying there is a duplicate?

 

If the ID is non existent you won't get a duplicate error. I *think* you are trying to run an insert of multiple records where some of those records may be duplicates. But your question and example aren't clear. Please explain again.

[karimali831]

Your request isn't making sense. You say you want to JOIN tables but then you are asking if you can INSERT. Based upon your examples you are trying to run the same insert statement twice.

 

Basically I am trying to insert rows into an existing table which has hundreds of ID duplicates therefore it won't insert. Same IDs but different records so my question is if I can insert and it changes the ID duplicates to another ID that does not exist in the table?

[Pikachu2000]

Why are you trying to do that? What is there now, what are you trying to insert, and what is the end result you're hoping to achieve?

[karimali831]

Why are you trying to do that? = I'm trying to combine both my user tables together but some have the same ID so it won't execute.

What is there now = the new records

what are you trying to insert = keeping the old records and inserting the new ones

what is the end result you're hoping to achieve? = just to put all records together and each having different ID

[Pikachu2000]

The ID (I'm assuming you mean the primary key for the table) should be inconsequential. It should not be specifically inserted, it should be an auto-incrementing integer. What is the table structure?

[harristweed]

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

[karimali831]

Ok, yes ID is the primary key and you say I should not insert it.

 

(7,1,8,0,1,0),

(8,1,9,0,1,0),

(9,1,10,0,1,0),

(31,1,29,0,0,0),

(26,1,27,0,1,0),

(17,1,17,0,1,0),

(13,1,15,0,1,0),

(107,4,38,0,1,0),

(18,1,18,0,1,0),

(19,1,19,0,1,0),

(20,1,20,0,1,0),

(22,1,21,0,1,0),

(23,1,22,0,1,0),

 

The list above is hundreds and hundreds. Is there a quick way to remove the first column?

 

(7,1,8,0,1,0) == (1,8,0,1,0) etc

[Psycho]

Ok, this is an example where more information would have been better. It wasn't until your third post that what you are trying to achieve made sense:

I'm trying to combine both my user tables together but some have the same ID so it won't execute...

 

I was going to suggest ON DUPLICATE KEY in your query (as harristweed suggested) until I read that. The solution to this should be very simple. I will assume that the ID field for your tables is also an auto-increment field. So, the easy solution is DON'T INCLUDE THE ID FIELD IN THE INSERT QUERY.

 

In fact, if both tables are in the same database (copy one into a temp table in the other if they aren't) then you only need a single query to combine the records into one table:

 

Not tested, but this should be about right

INSERT INTO users (col1, col2)
VALUES (SELECT col1, col2 FROM users_temp)