CODEWITHSUNDEEP
sqlsql-server
JTR
JTR

Reputation: 333

Trying to insert into select from two tables that have one to many relations

I have tbl1 

IDa | year | field2 |
----+------+---------
1   | 2015 | bbb    |
2   | 2015 | aaa    |
3   | 2015 | ccc    |
---------------------

I'm using INSERT INTO SELECT to copy all the data from 2015, but with change in year into 2016

INSERT INTO tbl1 (year, field2) SELECT '2016', field2 from tbl1 WHERE year = '2015'

and return the data like this

IDa | year | field2 |
----+------+---------
1   | 2015 | bbb    |
2   | 2015 | aaa    |
3   | 2015 | ccc    |
4   | 2016 | bbb    |
5   | 2016 | aaa    |
6   | 2016 | ccc    |
---------------------

Now, I have another table tbl2 that have a foreign key to IDa in tbl1, which the relations is one to many like this

IDb | year | IDa | field3 |
----+------+-----+---------
1   | 2015 | 1   | ddd    |
2   | 2015 | 1   | eee    |
3   | 2015 | 2   | fff    |
4   | 2015 | 3   | ggg    |
5   | 2015 | 3   | hhh    |
6   | 2015 | 3   | iii    |
---------------------------

And now, with INSERT INTO SELECT I also want to copy data from tbl2 in year 2015 and change it to 2016, but with IDa in year 2016

I've tried this 

INSERT INTO tbl2 (year, IDa, field3)
    SELECT year, (SELECT IDa FROM tbl1 WHERE year = '2016'), field3
    FROM tbl2
    WHERE year = '2015'

But this query getting error because one field return multiple value at the time.

So, how to make the right query for this? I want the result is like this

IDb | year | IDa | field3 |
----+------+-----+---------
1   | 2015 | 1   | ddd    |
2   | 2015 | 1   | eee    |
3   | 2015 | 2   | fff    |
4   | 2015 | 3   | ggg    |
5   | 2015 | 3   | hhh    |
6   | 2015 | 3   | iii    |
7   | 2016 | 4   | ddd    |
8   | 2016 | 4   | eee    |
9   | 2016 | 5   | fff    |
10  | 2016 | 6   | ggg    |
11  | 2016 | 6   | hhh    |
12  | 2016 | 6   | iii    |
---------------------------

Is it possible?

I'm appreciate all the answers, thanks for help.

Upvotes: 2

Views: 365

Answers (3)

Paul Spiegel
Paul Spiegel

Reputation: 31812

Add column oIDa (original ID) to tbl1.

ALTER TABLE tbl1 ADD oIDa INT;

Copy data with the original ID, so you can use it later.

INSERT INTO tbl1 (year, field2, oIDa) SELECT '2016', field2, IDa from tbl1 WHERE year = '2015';

Now you have a column, that you can join on, to find the new generated IDa value:

INSERT INTO tbl2 (year, IDa, field3)
    SELECT year, tbl1.IDa, tbl2.field3
    FROM tbl2
    JOIN tbl1 ON tbl1.oIDa = tbl2.IDa
    WHERE tbl2.year = 2015;

Now you can remove the new column:

ALTER TABLE tbl1 DROP COLUMN oIDa;

Upvotes: 2

MatBailie
MatBailie

Reputation: 86735

As it stands your sub query doesn't return the IDa for one row, but all of the values for the whole year. (This is a problem because you can't put all those values in one field in one row.)

To fix your exact syntax you need to "correlate" the sub query against the table you're reading from, so that you get only one result per row, for your IDa lookup...

Unfortunately, in your case, that gets very messy. You need to look up what the original IDa was in table1, then look for the matching row in the same table but for the next year...

INSERT INTO
    tbl2 (
        year,
        IDa,
        field3
    )
SELECT
    year + 1,
    (SELECT IDa
       FROM tbl1
      WHERE year   = tbl2.year + 1
        AND field2 = (SELECT field2
                        FROM tbl1
                       WHERE IDa = tbl2.IDa
                     )
    ),
    field3
FROM
    tbl2
WHERE
    year = '2015'

That said, there is a simpler way, using joins...

INSERT INTO
    tbl2 (
        year,
        IDa,
        field3
    )
SELECT
    newer.year,
    newer.IDa,
    tbl2.field3
FROM
    tbl2
INNER JOIN
    tbl1   older
       ON  older.IDa    = tbl2.IDa
INNER JOIN
    tbl1   newer
       ON  newer.year   = older.year + 1
       AND newer.field2 = older.field2
WHERE
    tbl2.year = 2015

EDIT

If field2 doesn't exist, you have the problem of not knowing which IDa to pick in each case.

That leaves two options, as I see it:
- Use the approach from @PaulSpiegel
- Manually introduce a proxy

Personally, this is sounding more and more convoluted, and a basic redesign sounds like it's going to be beneficial.

If you're unable to re-design or use the method from @PaulSpiegel, there is an option...

INSERT INTO
    tbl2 (
        year,
        IDa,
        field3
    )
WITH
   ordered_tbl1
(
    SELECT
        ROW_NUMBER() OVER (PARTITION BY year ORDER BY year)   AS ordinal,
        tbl1.*
    FROM
        tbl1
)
SELECT
    newer.year,
    newer.IDa,
    tbl2.field3
FROM
    tbl2
INNER JOIN
    ordered_tbl1    older
       ON  older.IDa     = tbl2.IDa
INNER JOIN
    ordered_tbl1    newer
       ON  newer.year    = older.year + 1
       AND newer.ordinal = older.ordinal
WHERE
    tbl2.year = 2015

This assumes the only fields in tbl1 are year and IDa. If you have other fields in tbl1 then they should be included in the ROW_NUMBER()'s ORDER BY clause, to ensure that the rows appear in the same order in both years.

If this doesn't make sense to you, then you probably shouldn't use it. Instead re-design or use @PaulSpiegel's approach.

Upvotes: 2

mjharper
mjharper

Reputation: 145

I don't 100% understand your data - so I'm not sure if Field2 and Field3 will always be the same for each year. If it is then this should work:

INSERT INTO tbl2 (year, IDa, field3)
SELECT c.Year,
       c.Ida,
       a.field3
  FROM tbl2 a
       INNER JOIN tbl1 b
               ON a.IDa = b.Ida
       INNER JOIN tbl1 c
               ON field2 = field2
              AND c.year = 2016
 WHERE a.year = '2015';

Upvotes: 0

Related Questions