У меня есть простая таблица mysql:
CREATE TABLE IF NOT EXISTS `pers` (
`persID` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(35) NOT NULL,
`gehalt` int(11) NOT NULL,
`chefID` int(11) DEFAULT NULL,
PRIMARY KEY (`persID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
INSERT INTO `pers` (`persID`, `name`, `gehalt`, `chefID`) VALUES
(1, 'blb', 1000, 3),
(2, 'as', 1000, 3),
(3, 'chef', 1040, NULL);
Я попытался запустить следующее обновление, но получаю только ошибку 1093:
UPDATE pers P
SET P.gehalt = P.gehalt * 1.05
WHERE (P.chefID IS NOT NULL
OR gehalt <
(SELECT (
SELECT MAX(gehalt * 1.05)
FROM pers MA
WHERE MA.chefID = MA.chefID)
AS _pers
))
Я искал ошибку и нашел из mysql следующую страницу http://dev.mysql.com/doc/refman/5.1/en/subquery-restrictions.html, но это мне не помогает.
Что мне делать, чтобы исправить sql-запрос?
Проблема заключается в том, что MySQL, по какой бы то ни было причине, не позволяет вам писать такие запросы:
UPDATE myTable
SET myTable.A =
(
SELECT B
FROM myTable
INNER JOIN ...
)
То есть, если вы делаете UPDATE
/INSERT
/DELETE
в таблице, вы не можете ссылаться на эту таблицу во внутреннем запросе (вы можете, но ссылаться поле из этой внешней таблицы...)
Решение заключается в замене экземпляра myTable
в подзапросе с помощью (SELECT * FROM myTable)
, как этот
UPDATE myTable
SET myTable.A =
(
SELECT B
FROM (SELECT * FROM myTable) AS something
INNER JOIN ...
)
Это, по-видимому, приводит к тому, что необходимые поля неявно копируются во временную таблицу, поэтому это разрешено.
Я нашел это решение здесь. Заметка из этой статьи:
Вы не хотите просто
SELECT * FROM table
в подзапросе в реальной жизни; Я просто хотел, чтобы эти примеры были простыми. В действительности вы должны выбрать только столбцы, которые вам нужны в этом самом внутреннем запросе, и добавить хорошее предложениеWHERE
, чтобы ограничить результаты.
Вы можете сделать это в три этапа:
CREATE TABLE test2 AS
SELECT PersId
FROM pers p
WHERE (
chefID IS NOT NULL
OR gehalt < (
SELECT MAX (
gehalt * 1.05
)
FROM pers MA
WHERE MA.chefID = p.chefID
)
)
...
UPDATE pers P
SET P.gehalt = P.gehalt * 1.05
WHERE PersId
IN (
SELECT PersId
FROM test2
)
DROP TABLE test2;
или
UPDATE Pers P, (
SELECT PersId
FROM pers p
WHERE (
chefID IS NOT NULL
OR gehalt < (
SELECT MAX (
gehalt * 1.05
)
FROM pers MA
WHERE MA.chefID = p.chefID
)
)
) t
SET P.gehalt = P.gehalt * 1.05
WHERE p.PersId = t.PersId
CREATE TABLE
- я надеюсь, что автору было об этом известно. Однако это единственное решение? Или запрос может быть переписан с помощью подзапросов или объединений? И почему (не) это сделать?
UPDATE Pers P
читать UPDATE pers P
?
Сделайте временную таблицу (tempP) из подзапроса
UPDATE pers P
SET P.gehalt = P.gehalt * 1.05
WHERE P.persID IN (
SELECT tempP.tempId
FROM (
SELECT persID as tempId
FROM pers P
WHERE
P.chefID IS NOT NULL OR gehalt <
(SELECT (
SELECT MAX(gehalt * 1.05)
FROM pers MA
WHERE MA.chefID = MA.chefID)
AS _pers
)
) AS tempP
)
Я ввел отдельное имя (псевдоним) и дал новое имя столбцу 'persID' для временной таблицы
SELECT ( SELECT MAX(gehalt * 1.05)..
- первый SELECT
не SELECT ( SELECT MAX(gehalt * 1.05)..
ни один столбец.
В Mysql вы не можете обновить одну таблицу подзапросом в той же таблице.
Вы можете разделить запрос на две части или сделать
UPDATE TABLE_A AS A INNER JOIN TABLE_A AS B ON A.field1 = B.field1 SET field2 = ?
SELECT ... SET
? Я никогда не слышал об этом.
Это довольно просто. Например, вместо написания:
INSERT INTO x (id, parent_id, code) VALUES (
NULL,
(SELECT id FROM x WHERE code='AAA'),
'BBB'
);
вам следует написать
INSERT INTO x (id, parent_id, code)
VALUES (
NULL,
(SELECT t.id FROM (SELECT id, code FROM x) t WHERE t.code='AAA'),
'BBB'
);
или аналогичный.
Подход, отправленный BlueRaja, медленный, я изменил его как Я использовал для удаления дубликатов из таблицы. В случае, если это поможет любому, у кого большие столы Исходный запрос
delete from table where id not in (select min(id) from table group by field 2)
Это занимает больше времени:
DELETE FROM table where ID NOT IN(
SELECT MIN(t.Id) from (select Id,field2 from table) AS t GROUP BY field2)
Более быстрое решение
DELETE FROM table where ID NOT IN(
SELECT x.Id from (SELECT MIN(Id) as Id from table GROUP BY field2) AS t)
В качестве ссылки вы также можете использовать Mysql Variables для сохранения временных результатов, например:
SET @v1 := (SELECT ... );
UPDATE ... SET ... WHERE x=@v1;
Если вы пытаетесь прочитать fieldA из таблицы A и сохранить его в поле B в той же таблице, когда fieldc = fieldd, вы можете подумать об этом.
UPDATE tableA,
tableA AS tableA_1
SET
tableA.fieldB= tableA_1.filedA
WHERE
(((tableA.conditionFild) = 'condition')
AND ((tableA.fieldc) = tableA_1.fieldd));
Выше кода копирует значение из поля A в поле B, когда поле условия удовлетворяет вашему условию. это также работает в ADO (например, в доступе)
источник: попробовал себя
MariaDB снял это с 10.3.x (оба для DELETE
и UPDATE
):
ОБНОВЛЕНИЕ - заявления с одним и тем же источником и целью
Из MariaDB 10.3.2 операторы UPDATE могут иметь одинаковый источник и цель.
До MariaDB 10.3.1 следующий оператор UPDATE не работал:
UPDATE t1 SET c1=c1+1 WHERE c2=(SELECT MAX(c2) FROM t1); ERROR 1093 (HY000): Table 't1' is specified twice, both as a target for 'UPDATE' and as a separate source for data
Из MariaDB 10.3.2 утверждение выполняется успешно:
UPDATE t1 SET c1=c1+1 WHERE c2=(SELECT MAX(c2) FROM t1);
УДАЛИТЬ - тот же источник и целевая таблица
До тех пор пока MariaDB 10.3.1, удаление из таблицы с тем же источником и целью не было возможным. Из MariaDB 10.3.1 это стало возможным. Например:
DELETE FROM t1 WHERE c1 IN (SELECT b.c1 FROM t1 b WHERE b.c2=0);