Заменить столбец с использованием идентификатора в MySQL
(Довольно новичок в SQL, поэтому, пожалуйста, потерпите меня :)) У меня есть таблица с двумя записями:
Таблица 1:
| Я БЫ | Владелец | Донор | Отец | ЕТ1 |
|---|---|---|---|---|
| 12 | Джонс | Дейзи | Боб | 18 |
| 18 | Оуэнс | Дейзи | Боб | 834 |
Как видите, у этих двух записей один и тот же Донор и один и тот же Отец. Мне также нужно, чтобы они использовали один и тот же номер ЕТ1. В настоящее время первая запись имеет ЕТ1, который на самом деле является идентификатором второй записи, но мне нужно, чтобы они обе использовали один и тот же ЕТ1. Я использовал следующий код, и он входит, но фактически не обновляет ни одну из записей. Я думал, что это будет просто, но может ли кто-нибудь пролить свет на то, что я делаю неправильно?
update table1 t
set t.ET1 = t.ET1
where t.ET1 = t.ID
where t.ET1 = t.ID никогда не верен в пределах одной строки. Where сравнивает только значения в отдельной строке.
Обычно, если у них один и тот же донор и отец, они должны иметь один и тот же ET1. ЕТ1 - наша самая важная функция, поскольку именно так мы идентифицируем записи - мы всегда будем искать ЕТ1, но, возможно, мы сменили владельца, что означает, что вводится новая запись и меняется только владелец. Я ищу запрос, который поможет обновить некорректные записи, подобные приведенной выше.
И set t.ET1 = t.ET1 просто установит значение для себя. Опять же, t относится только к одной строке за раз.
Вы должны использовать как минимум 2 копии таблицы в UPDATE. На самом деле вы должны использовать рекурсивный CTE в источнике данных для обновления или создать простой запрос обновления с двумя таблицами и выполнять его до тех пор, пока количество обновленных строк не станет нулевым.
Basically if they share the same donor and sire they need to share the same ET1 ... да, мы это уже знаем. но из примеров данных неясно, следует ли считать 18 или 834 «правильным» значением и как компьютер должен это определять.
PS. Ваше обновление навсегда уничтожит информацию (но эта информация может иметь смысл! По крайней мере, в ее исторической части). Рекомендую создать и заполнить дополнительный столбец, например RootET1.
Предположительно, самый ранний идентификатор - это правильный et1 - но данные имена не уникальны, поэтому присоединение к донору и отцу может показаться неуместным.
@ADyson, ладно ... я имею в виду, что теперь вы упомянули об этом. Как я могу конкретно указать, что 834 будет правильным значением, без конкретного указания этого, особенно если у меня было много данных?
@ P.Salmon в моей реальной таблице донор, отец и владелец - это числа, относящиеся к их идентификаторам в других таблицах. Я использовал настоящие имена для демонстрации :)
Вам необходимо определить общее правило, по которому он может быть вычислен в любой паре строк. Также вам нужно определить, как ряд является парой - вы говорите, что это когда донор и отец совпадают, но предположительно Дейзи и Боб создали одно, а не одно потомство? Так что этого недостаточно. например возможно, дело в том, что когда E1 строки A равен идентификатору строки B, они являются парой, и тогда ET1 строки B должен быть правильным. Это относится ко всем вашим данным? Как только вы определите правило, мы сможем решить, как представить его в коде.
@ADyson: Хорошо, опять же, в этом есть смысл. Да, это относится не ко всем, но ко многим моим данным в этой таблице. Я полагаю, что общее правило состоит в том, что когда ET1 строки A = ID строки B, у них один и тот же донор и отец, но другой владелец.
Хорошо, тогда. Не проверено, но, надеюсь, дает вам общее представление: UPDATE table1 t1 SET t1.ET1 = t2.ET1 FROM table1 t1 INNER JOIN table1 t2 ON t1.ET1 = t2.ID AND t1.Donor = t2.Donor AND t1.Sire = t2.Sire AND t1.Owner <> t2.Owner. В основном вы присоединяете таблицу к самой себе, чтобы вы могли сопоставить разные строки в одной и той же таблице на основе связывания ET1 / ID, а затем она фильтруется по дополнительным критериям о владельцах / производителях / донорах. Это может работать не так, как есть, я не тестировал синтаксис и т. д., Но, надеюсь, вы видите, к чему все идет.
Ответы 1
Перед обновлением я настоятельно рекомендую следующие две меры предосторожности:
- Сделайте резервную копию таблицы - если вы обновите ее неправильно, вы можете восстановить ее из резервной копии.
- Выполните запрос
SELECT, прежде чем переходить кUPDATE, чтобы узнать, что вы будете обновлять.
Кроме того, я второй комментарий @ Akina, добавив колонку ссылок на исходный ET1 или обновленный ET1.
Итак, давайте посмотрим на этот запрос:
SELECT * FROM table1 a
JOIN table1 b
ON a.Donor=b.Donor
AND a.Sire=b.Sire
AND a.ET1 > b.ET1;
table1 в запросе самосоединяется с условием ON в соответствии с вашим требованием, и я предполагаю, что наименьшее значение ET1 в этом состоянии будет считаться значением ET1 для любой записи, имеющей больший ET1 (или больший идентификатор?). Этот запрос будет делать именно это, и после подтверждения его запросом SELECT просто измените запрос на следующее:
UPDATE table1 a
JOIN table1 b
ON a.Donor=b.Donor
AND a.Sire=b.Sire
AND a.ET1 > b.ET1
SET a.ET1=b.ET1;
С предложением Акины, я думаю, это будет примерно так:
Добавьте новый столбец и обновите новый столбец исходным значением ET1.
ALTER TABLE table1 ADD COLUMN RootET1 INT;
UPDATE table1 SET RootET1=ET1;
Это даст вам что-то вроде этого:
| Я БЫ | Владелец | Донор | Отец | ЕТ1 | RootET1 |
|---|---|---|---|---|---|
| 12 | Джонс | Дейзи | Боб | 18 | 18 |
| 18 | Оуэнс | Дейзи | Боб | 834 | 834 |
После этого вы запускаете тот же запрос UPDATE, что и выше. Теперь у вас есть обновленное значение ET1 и исходное значение ET1 в RootET1.
«Мне нужно, чтобы они оба использовали один и тот же ЕТ1» - какова логика определения того, какой из них?