user1170330 user1170330 - 7 months ago 12
SQL Question

Duplicate entries in tables

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.

Answer

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.

Comments