[SOLVED] Insert multiple based on select

Issue

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

Solution

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

Answered By – EdGzi

Answer Checked By – Cary Denson (BugsFixing Admin)

Leave a Reply

Your email address will not be published.