Error number: 1452; Symbol: ER_NO_REFERENCED_ROW_2; SQLSTATE: 23000
Message: Cannot add or update a child row: a foreign key constraint fails (%s)
InnoDB reports this error when you try to add a row but there is no parent row, and a foreign key constraint fails. Add the parent row first.
Или перед вставкой нужно создать запрос на проверку существования такой записи в связующей таблице?
Есть ли разница джоинов этих двух строк, с точки зрения нагрузки на БД?
пробовал делать это через "?" и "*" , но не получается
PreparedStatement ps = plugin.SQL.getConnection().prepareStatement("UPDATE PlayerBlocks SET ?? = ? WHERE UUID=?");WITH cte AS (
SELECT *, SUM(rating) OVER (ORDER BY id) cum_rating
FROM table
)
SELECT
FROM cte t1
JOIN (SELECT RAND() * MAX(cum_rating) rnd_rating
FROM cte) t2 ON t2.rnd_rating BETWEEN t1.cum_rating - t1.rating AND t1.cum_ratingSELECT service.id AS service_id, COUNT(subs.id) AS c
FROM ( SELECT 13 id UNION
SELECT 74 UNION
SELECT 71 UNION
SELECT 72 ) AS service
LEFT JOIN Subscriptions subs ON subs.service_id = service.id AND subs.msisdn=992777757031
GROUP BY service.id
ORDER BY c DESC;WITH
cte1 AS ( SELECT id, name, ROW_NUMBER() OVER (ORDER BY name) rn
FROM t1 ),
cte2 AS ( SELECT id, name, ROW_NUMBER() OVER (ORDER BY name) rn
FROM t2 )
SELECT cte1.id id1, cte1.name name1, cte2.id id2, cte2.name name2
FROM cte1
JOIN cte2 USING (rn, id);SELECT u.*, t.type_name, t.description
FROM users AS u
LEFT JOIN type_user AS tu ON tu.id_user = u.id
LEFT JOIN types AS t ON tu.id_type = t.id
WHERE u.id = 1;WITH RECURSIVE
cte1 AS ( SELECT MIN(DATE(create_date)) mindate, MAX(DATE(create_date)) maxdate
FROM shops
UNION ALL
SELECT MIN(DATE(create_date)) mindate, MAX(DATE(create_date)) maxdate
FROM users ),
cte2 AS ( SELECT MIN(mindate) mindate, MAX(maxdate) maxdate
FROM cte1 ),
dates AS ( SELECT mindate thedate, maxdate
FROM cte2
UNION ALL
SELECT thedate + INTERVAL 1 DAY, maxdate
FROM dates
WHERE thedate < maxdate ),
shopstat AS ( SELECT DATE(create_date) thedate, COUNT(*) cnt
FROM shops
GROUP BY thedate ),
userstat AS ( SELECT DATE(create_date) thedate, COUNT(*) cnt
FROM users
GROUP BY thedate )
SELECT thedate `date`,
COALESCE(shopstat.cnt, 0) shops,
COALESCE(userstat.cnt, 0) users
FROM dates
LEFT JOIN shopstat USING (thedate)
LEFT JOIN userstat USING (thedate)SELECT event,
CONVERT_TZ(time, '+00:00', '-04:00') AS time1
FROM events
WHERE CONVERT_TZ(time, '+00:00', '-04:00') between '2021-06-24 00:00:00' AND '2021-06-24 23:59:59'
AND lang = 'ru'SELECT event,
CONVERT_TZ(time, '+00:00', '-04:00') AS time1
FROM events
WHERE lang = 'ru'
HAVING time1 between '2021-06-24 00:00:00' AND '2021-06-24 23:59:59'UPDATE users u1
CROSS JOIN users u2
SET u1.amount = u1.amount - $summa,
u2.amount = u2.amount + $summa
WHERE u1.id = $client
AND u2.id = $shop;SELECT 500 queue, operator, os.avg_os, ss.avg_ss
FROM ( SELECT operator,AVG(valuation) avg_os
FROM oper_survey
WHERE queue = '500' AND `date` BETWEEN '2021-06-01' AND '2021-06-16'
GROUP BY operator ) os
JOIN ( SELECT operator, AVG(valuation) avg_ss
FROM service_survey
WHERE queue = '500' AND `date` BETWEEN '2021-06-01' AND '2021-06-16'
GROUP BY operator ) ss USING (operator)SELECT 500 queue, operator, COALESCE(os.avg_os, 0) avg_os, COALESCE(ss.avg_ss, 0) avg_ss
FROM ( SELECT operator FROM oper_survey WHERE queue = '500'
UNION
SELECT operator FROM service_survey WHERE queue = '500' ) op
LEFT JOIN ( SELECT operator,AVG(valuation) avg_os
FROM oper_survey
WHERE queue = '500' AND `date` BETWEEN '2021-06-01' AND '2021-06-16'
GROUP BY operator ) os USING (operator)
LEFT JOIN ( SELECT operator, AVG(valuation) avg_ss
FROM service_survey
WHERE queue = '500' AND `date` BETWEEN '2021-06-01' AND '2021-06-16'
GROUP BY operator ) ss USING (operator)SELECT users.name AS username,
count(orders.id) AS orders_count
FROM users
JOIN orders ON orders.user_id=users.id
GROUP BY users.name