#mysql #sql
#mysql #sql
Вопрос:
Я пытаюсь вставить данные из одного столбца в другой. Столбцы, которые я хочу обновить, — это Hrstotal и Empcnt.
-------- ---------------------- -------- --------- ------------ ------------ --------- ---------- --------
| projno | projname | deptno | respemp | prstdate | prendate | majproj | Hrstotal | Empcnt |
-------- ---------------------- -------- --------- ------------ ------------ --------- ---------- --------
| AD3100 | Admin Services | D01 | 000010 | 2014-01-01 | 2015-02-01 | NULL | NULL | 0 |
| AD3110 | General AD Systems | D21 | 000070 | 2014-01-01 | 2015-02-01 | AD3100 | NULL | 0 |
| AD3111 | Payroll Programming | D21 | 000230 | 2014-01-01 | 2015-02-01 | AD3110 | NULL | 0 |
| AD3112 | Personnel Programmg | D21 | 000250 | 2014-01-01 | 2015-02-01 | AD3110 | NULL | 0 |
| AD3113 | Account.Programming | D21 | 000270 | 2014-01-01 | 2015-02-01 | AD3110 | NULL | 0 |
| IF1000 | Query Services | C01 | 000030 | 2014-01-01 | 2015-02-01 | NULL | NULL | 0 |
| IF2000 | User Education | C01 | 000030 | 2014-01-01 | 2015-02-01 | NULL | NULL | 0 |
| MA2100 | Weld Line Automation | D01 | 000010 | 2014-01-01 | 2015-02-01 | NULL | NULL | 0 |
| MA2110 | W L Programming | D11 | 000060 | 2014-01-01 | 2015-02-01 | MA2100 | NULL | 0 |
| MA2111 | W L Program Design | D11 | 000220 | 2014-01-01 | 2014-12-01 | MA2110 | NULL | 0 |
| MA2112 | W L Robot Design | D11 | 000150 | 2014-01-01 | 2014-12-01 | MA2110 | NULL | 0 |
| MA2113 | W L Prod Cont Progs | D11 | 000160 | 2014-02-15 | 2014-12-01 | MA2110 | NULL | 0 |
| OP1000 | Operation Support | E01 | 000050 | 2014-01-01 | 2015-02-01 | NULL | NULL | 0 |
| OP1010 | Operation | E11 | 000090 | 2014-01-01 | 2015-02-01 | OP1000 | NULL | 0 |
| OP2000 | GEN Systems Services | E01 | 000050 | 2014-01-01 | 2015-02-01 | OP1000 | NULL | 0 |
| OP2010 | Systems Support | E21 | 000100 | 2014-01-01 | 2015-02-01 | OP2000 | NULL | 0 |
| OP2011 | SCP Systems Support | E21 | 000320 | 2014-01-01 | 2015-02-01 | OP2010 | NULL | 0 |
| OP2012 | Applications Support | E21 | 000330 | 2014-01-01 | 2015-02-01 | OP2010 | NULL | 0 |
| OP2013 | DB/DC Support | E21 | 000340 | 2014-01-01 | 2015-02-01 | OP2010 | NULL | 0 |
| PL2100 | Weld Line Planning | B01 | 000020 | 2014-01-01 | 2014-09-15 | MA2100 | NULL | 0 |
-------- ---------------------- -------- --------- ------------ ------------ --------- ---------- --------
Значения, которые я хочу, это Empcnt = для записи количества сотрудников, работающих над каждым проектом, и Hrstotal = для записи общего количества часов, которые будут потрачены на каждый проект всеми назначенными ему сотрудниками. Эти данные могут быть вычислены из этой таблицы:
-------- -------- -------
| empno | projno | hours |
-------- -------- -------
| 000020 | PL2100 | 31 |
| 000030 | IF1000 | 11 |
| 000030 | IF2000 | 10 |
| 000050 | OP1000 | 16 |
| 000050 | OP2000 | 15 |
| 000060 | MA2110 | 8 |
| 000060 | MA2111 | 7 |
| 000060 | MA2112 | 7 |
| 000060 | MA2113 | 7 |
| 000070 | AD3110 | 8 |
| 000070 | AD3111 | 7 |
| 000070 | AD3112 | 7 |
| 000070 | AD3113 | 7 |
| 000090 | OP1010 | 31 |
| 000100 | OP2010 | 8 |
| 000100 | OP2011 | 7 |
| 000100 | OP2012 | 7 |
| 000100 | OP2013 | 7 |
| 000130 | IF1000 | 10 |
| 000130 | IF2000 | 10 |
| 000140 | IF1000 | 10 |
| 000140 | IF2000 | 10 |
| 000150 | MA2110 | 7 |
| 000150 | MA2111 | 7 |
| 000150 | MA2112 | 7 |
| 000150 | MA2113 | 7 |
| 000160 | MA2110 | 7 |
| 000160 | MA2111 | 7 |
| 000160 | MA2112 | 7 |
| 000160 | MA2113 | 7 |
| 000170 | MA2110 | 7 |
| 000170 | MA2111 | 7 |
| 000170 | MA2112 | 7 |
| 000170 | MA2113 | 7 |
| 000180 | MA2110 | 7 |
| 000180 | MA2111 | 7 |
| 000180 | MA2112 | 7 |
| 000180 | MA2113 | 7 |
| 000190 | MA2110 | 7 |
| 000190 | MA2111 | 7 |
| 000190 | MA2112 | 7 |
| 000190 | MA2113 | 7 |
| 000200 | MA2110 | 7 |
| 000200 | MA2111 | 7 |
| 000200 | MA2112 | 7 |
| 000200 | MA2113 | 7 |
| 000210 | MA2110 | 7 |
| 000210 | MA2111 | 7 |
| 000210 | MA2112 | 7 |
| 000210 | MA2113 | 7 |
| 000220 | MA2110 | 7 |
| 000220 | MA2111 | 7 |
| 000220 | MA2112 | 7 |
| 000220 | MA2113 | 7 |
| 000230 | AD3110 | 8 |
| 000230 | AD3111 | 7 |
| 000230 | AD3112 | 7 |
| 000230 | AD3113 | 7 |
| 000240 | AD3110 | 7 |
| 000240 | AD3111 | 7 |
| 000240 | AD3112 | 7 |
| 000240 | AD3113 | 7 |
| 000250 | AD3110 | 7 |
| 000250 | AD3111 | 7 |
| 000250 | AD3112 | 7 |
| 000250 | AD3113 | 7 |
| 000260 | AD3110 | 7 |
| 000260 | AD3111 | 7 |
| 000260 | AD3112 | 7 |
| 000260 | AD3113 | 7 |
| 000270 | AD3110 | 7 |
| 000270 | AD3111 | 7 |
| 000270 | AD3112 | 7 |
| 000270 | AD3113 | 7 |
| 000280 | OP1010 | 31 |
| 000290 | OP1010 | 30 |
| 000300 | OP1010 | 30 |
| 000310 | OP1010 | 30 |
| 000320 | OP2010 | 8 |
| 000320 | OP2011 | 7 |
| 000320 | OP2012 | 7 |
| 000320 | OP2013 | 7 |
| 000330 | OP2010 | 7 |
| 000330 | OP2011 | 7 |
| 000330 | OP2012 | 7 |
| 000330 | OP2013 | 7 |
| 000340 | OP2010 | 7 |
| 000340 | OP2011 | 7 |
| 000340 | OP2012 | 7 |
| 000340 | OP2013 | 7 |
Запросы, которые я пробовал с каждой ошибкой
UPDATE Proj set Empcnt = (SELECT COUNT(*) FROM Pworks GROUP BY empno);
ERROR 1242 (21000): Subquery returns more than 1 row
mysql> UPDATE Proj p set Empcnt = (SELECT COUNT(*) FROM Pworks WHERE projno = p.projno GROUP BY empno);
ERROR 1242 (21000): Subquery returns more than 1 row
INSERT INTO Proj(Empcnt) SELECT COUNT(*) FROM Pworks INNER JOIN Proj ON empno = respemp GROUP BY empno;
ERROR 1364 (HY000): Field 'projno' doesn't have a default value
UPDATE Proj SET Empcnt = (SELECT COUNT(*) FROM Pworks NATURAL JOIN Proj GROUP BY Proj.projno);
ERROR 1093 (HY000): You can't specify target table 'Proj' for update in FROM clause
Ответ №1:
Вы можете использовать коррелированный подзапрос:
UPDATE Proj p
SET Empcnt = (SELECT COUNT(*)
FROM Pworks pw
WHERE pw.projno = p.projno
------------------------^ this is the correlation clause that counts the employees on each project
)