CODEWITHSUNDEEP
mysqlsql
GPiter
GPiter

Reputation: 809

Insert multiple based on select

I have this query

INSERT INTO page_role (page_id, role_id)
SELECT 
  (SELECT id FROM pages WHERE name = 'masterdata'),
  (SELECT id FROM roles WHERE name = 'PropertyOwner')

But I have the error :

Subquery return more than 1 row

How I can handle that ?

This select returning one result :

SELECT id 
FROM pages 
WHERE name = 'masterdata'

but this one

SELECT id 
FROM roles 
WHERE name = 'PropertyOwner'

more than one.

Modified query :

INSERT INTO page_role (page_id, role_id)
SELECT 45, id FROM roles r
 INNER JOIN page_role pr ON r.id = pr.role_id 
 WHERE r.name = 'PropertyOwner' AND pr.page_id <> 45

Error message : Duplicate entry 45-18223 for key page_role PRIMARY

Upvotes: 0

Views: 39

Answers (1)

DeveloperLV
DeveloperLV

Reputation: 1781

Try using IGNORE.

INSERT IGNORE INTO page_role (page_id, role_id)
SELECT 45, id FROM roles r
 INNER JOIN page_role pr ON r.id = pr.role_id 
 WHERE r.name = 'PropertyOwner' AND pr.page_id <> 45

Upvotes: 1

Related Questions