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
Help me please
CodePudding user response:
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