CODEWITHSUNDEEP
mysqlsql
Chewpers
Chewpers

Reputation: 2440

Insert duplicate rows for a new id based on result of another query

So I'll do my best to describe the query that I'm trying to build.

I have a table I'll call user_records that has some data (several rows) for a relational ID, say userId (from a table users). For each row, I need to duplicate each row for another user. I know I could run this:

INSERT INTO user_records (userId, column1, column2, ...)  
SELECT 10 as userId, column1, column2...  
FROM user_records 
WHERE userId = 1

This will copy the existing rows for userId 1 to userId 10.

But I want to run this for all userIds that are active and don't already exists in this table. So I want to basically execute this query first:

SELECT userID  
FROM users  
WHERE users.active = 1  
AND NOT EXISTS (  
SELECT * FROM user_records 
WHERE users.userId = user_records.userId)

Using JOINS or simply combining the 2 queries, can I run this query and replace the 10 in the former query so that it duplicates the rows for a series of userIds?

Thanks in advance!

Upvotes: 1

Views: 350

Answers (1)

sgeddes
sgeddes

Reputation: 62831

One way is to create a CROSS JOIN:

insert into user_records (userId, column1)
select u.userId, ur.column1
from user_records ur
  cross join users u 
where ur.userId = 1
  and u.active = 1
  and u.userId not in (select userId from user_records);

SQL Fiddle Demo

This will insert new rows into user_records for each userId that doesn't exist in the table, copying the data from UserId 1.

Upvotes: 1

Related Questions