Append and add information from rows above

Question

the title is not really helpful. Sorry. I better explain with code. I need a query in MySQL for the following problem:

Build schema:

CREATE TABLE AsIs 
( 
`PN` VARCHAR(10),
`Origin`VARCHAR(10),
`Sub`VARCHAR(10) 
);

INSERT INTO AsIs 
VALUES ('11111', 'List1', '22222'), ('98765', 'List2', '123456');

I create a table with the column subs:

CREATE TEMPORARY TABLE `Subs` SELECT  `sub` AS `PN` FROM AsIs;

I create a table to union the values from table AsIs and table subs :

CREATE TEMPORARY TABLE `UnionAsISwithSubs`
SELECT `PN` FROM AsIs UNION SELECT `PN` FROM `subs`;

And finally I create the last table to add the information from the AsIs table:

`CREATE TABLE `FinalList`
SELECT `UnionAsISwithSubs`.`PN`,`AsIs`.`Origin`,`AsIs`.`sub` 
FROM `UnionAsISwithSubs`
LEFT JOIN AsIs ON `UnionAsISwithSubs`.`PN` = `AsIs`.`PN;

There are for sure better ways to do this, but my main question is, how can i update the column 'Origin` with the values from the row above. So it should like this:

CREATE TABLE ShouldBe Like AsIs;
INSERT INTO ShouldBe 
VALUES('11111', 'List1', '22222'),('98765', 'List2', '123456'),
('22222', 'List1', ''),('123456', 'List2', '');

Many thanks in advance for your help.

I have simulate it with SQL fiddle. Please follow the link:

http://sqlfiddle.com/#!9/dd792/4

Answer 1

You can turn your AsIs table into a row set like the ShouldBe table without any temporary tables, using a single query like this:

SELECT
  PN,
  Origin,
  Sub
FROM
  AsIs

UNION ALL

SELECT
  Sub,
  Origin,
  ''
FROM
  AsIs
;

The first half of the query just lists the current contents of the AsIs table. The second half appends the same rows but this time putting the Sub column at the same position as PN in the first query and specifying an empty string in place of the original Sub.

The column names of the final resulting set are defined by the names that the first SELECT is returning. So, Sub values of the second SELECT will be returned as PN in the output and the empty strings as Sub – that is, for your example the output will look like this:

PN      Origin  Sub
------  -----   ------
11111   List1   22222
98765   List2   123456
22222   List1
123456  List2