0

Say I have 3 tables, each with a structure similar to this:

| id | customer_id | col1       | col2       | col3        |
|----|-------------|------------|------------|-------------|
| 1  | C100        | some value | some value | some values |
| 2  | C101        |            |            |             |
| 3  | C102        |            |            |             |

Now I want to duplicate their rows with a specific customer-id.

So in pseudo code something like:

DUPLICATE FROM tab1, tab2, tab3 WHERE customer_ID = C100 SET customer_ID = C987;

It would take the values of those 3 tables where the customer_id is C100 and just make in each table another entry, but with the new customer_id C987.

The 3 tables would look as follows:

| id | customer_id | col1       | col2       | col3        |
|----|-------------|------------|------------|-------------|
| 1  | C100        | some value | some value | some values |
| 2  | C101        |            |            |             |
| 3  | C102        |            |            |             |
| 4  | C987        | some value | some value | some value  |

Also, the structures in the tables are slightly different.

The id is a primary key, customer_id is unique.

Evgenij Reznik
  • 17,916
  • 39
  • 104
  • 181
  • So are you replacing the row? Your example does not have a row with customer_id =123. Are there any unique indexes on the table? Do you mean "3 tables" or "3 rows"? – Dan Apr 19 '16 at 13:39
  • @dan08 Sorry, my bad. I changed the id. Yes, both, the id and the customer-ID are unique. – Evgenij Reznik Apr 19 '16 at 13:40
  • There are several tables, in my example I changed it to 3. So yes, I mean 3 tables. Each of them has a different structure. – Evgenij Reznik Apr 19 '16 at 13:43

1 Answers1

3

Maybe you can do an insert-select:

INSERT INTO tab1
SELECT id, 'C987', col1, col2, col3
FROM   tab1
WHERE  customer_id = 'C100';

And you can do a similar query for tab2, and tab3.

Adrián
  • 419
  • 2
  • 17
  • Is it possible, not to specify each column separately? There are several columns, each with a different structure. I would like to avoid that. – Evgenij Reznik Apr 19 '16 at 13:44
  • @user1170330 I think you can't, because you need to explicit value 'C987'. – Adrián Apr 19 '16 at 13:48
  • @user1170330 - no, you have to list the field / column names specifically. – random_user_name Apr 19 '16 at 13:48
  • @cale_b Thanks! I think there is another problem because id is a primary key, and inserting a duplicate id will fail. But I have written this answer before knowing it was a PK :P – Adrián Apr 19 '16 at 13:50
  • Maybe there is something like `SELECT id, 'C987', *`? – Evgenij Reznik Apr 19 '16 at 13:51
  • @user1170330 - NOPE. There is NOT. See here:http://stackoverflow.com/a/5253322/870729 – random_user_name Apr 19 '16 at 13:52
  • @user1170330 Please take care of the id issue I mentioned to cale_b, before inserting. You should change the id because it is a PK. But you get the idea with this insert-select statement. – Adrián Apr 19 '16 at 13:57
  • 2
    I think to get around `id` being a primary key, you just specify the fields you want to insert and exclude id, (if its an auto-increment) – Dan Apr 19 '16 at 13:57