Question 113344 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to purposely duplicate specific rows in SELECT statement?

I want to duplicate a specific teacher so that they appear twice in the output of a SELECT statement.

Teacher Grade District
Mr. Smith 3rd West
Mrs. John 4th South
Mr. Cameron 2nd North
Mr. Cameron 2nd North
Kirk Horn 1st West

Desired result, after duplicating ‘Mr. Cameron’:

Teacher Grade District
Mr. Smith 3rd West
Mrs. John 4th South
Mr. Cameron 2nd North
Mr. Cameron 2nd North
Mr. Cameron 2nd North
Mr. Cameron 2nd North
Kirk Horn 1st West

What would a SELECT statement look like – without creating a new a table?

I want to do something like this but without the INSERT:
https://dba.stackexchange.com/questions/142414/easiest-way-to-duplicate-rows

Tags:

2

Answers


  1. 0

    You could use UNION ALL

    select Teacher,Grade,District
from test
union all
select Teacher,Grade,District
from test 
where Teacher='Mr. Cameron'
order by Teacher;

    https://dbfiddle.uk/rBpSL8NW

    If you want to force/predict how many duplicate values for Mr. Cameron you will add ,try below query which add only one duplicate value limit 1

    (select Teacher,Grade,District
 from test
 )
union all
(
select Teacher,Grade,District
from test 
where Teacher='Mr. Cameron'
limit 1 
  );

    https://dbfiddle.uk/uUX5QD3F

    Login or Signup to reply.
  2. 0

    If the table is big, and the filter is not selective and backed by an index, then this "trick" avoids a second sequential scan over the table – using PostgreSQL:

    SELECT t.*  -- or your list of columns
FROM   test t
     , generate_series(1, CASE WHEN t.teacher = 'Mr. Cameron' THEN 2 ELSE 1 END);

    fiddle

    It’s short syntax for a LATERAL join. See:

    If you need more copies, just replace ‘2’ above.

    Postgres has a hard time estimating the number of rows to expect with this construct, which may confuse query planning. Stu’s variant (doing the same) is slightly more expensive, but easier to estimate for query planning. Syntax needs to be adapted for Postgres:

    SELECT teacher, grade, district
FROM   test t
JOIN   LATERAL (VALUES (1),(2)) x(v) ON v = 1 OR teacher = 'Mr. Cameron';

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search