Зміна існуючих даних

Можливість зміни деяких чи всіх значень в існуючій рядку таблиці реалізується за допомогою команди UPDATE. Ця команда містить ключове слово UPDATE, де вказується ім'я використовуваної таблиці, і пропозиція SET, що визначає внесена зміна для необхідного полючи таблиці.

Наприклад, щоб змінити оцінки всіх студентів на 5, необхідно використовувати команду:

UPDATE USP SET OCENKA = 5;

Звичайно, набагато частіше приходиться вказувати не всі, а тільки визначені рядки таблиці для зміни єдиного значення, і з цією метою разом з UPDATE можна використовувати предикати. Наприклад, змінити оцінки на 5 по предметі з кодом 2003, можна виконавши таку команду:

UPDATE USP SET OCENKA = 5 WHERE PNUM = 2003;

За допомогою команди UPDATE можна модифікувати дані з декількох полів - пропозиція SET може призначати будь-як число стовпців, відокремлюваних комами. Всі зазначені призначення можуть бути зроблені для будь-якого табличного рядка, але тільки для однієї в кожен момент часу. Припустимо, що викладач Викулина пішла на пенсію, і замість її заняття повинна вести викладач Федченко. Це можна такою командою:

UPDATE TEACHERS SET TFAM = 'Федченко', TNAME = 'Світлана',

TOTCH = 'Геннадіївна', TDATE =01/09/1999 WHERE TNUM = 4001;

Ця команда передасть новому викладачу Федченко всі поточні навчальні предмети з таблиці PREDMET - у нашому прикладі це буде фізика. Однак майте на увазі, що модифікувати відразу багато таблиць в одній команді UPDATE не можна, а отже, не можна і використовувати назва (префікс) таблиці з ім'ям полючи для цієї команди. Т.е., наприклад, SET TEACHERS. TFAM = 'Федченко' викликає помилку.

У пропозиції SET команди UPDATE можна використовувати вираження, розташовуючи їх у списку для того полючи, яких необхідно змінити (нагадаємо, що в пропозиції VALUES команди INSERT вираження використовувати не можна). Наприклад, для того, щоб збільшити стипендію в 2 рази, можна використовувати наступну конструкцію:

UPDATE STUDENTS SET STIP = STIP*2;

При цьому щораз, коли команда посилається до зазначеного значення полючи в пропозиції SET, дія виробляється, зрозуміло, над ще не модифікованими даними поточної запису.

Крім того, можна використовувати більш складні предикати вибору запису для модифікації. Наприклад, якщо необхідно подвоїти тільки стипендію розміром 25.50, то команда буде наступною:

UPDATE STUDENTS SET STIP = STIP*2 WHERE STIP = 25.50;

Команда UPDATE може працювати з NULL значеннями. Так що, якщо необхідно змінити всі оцінки студентів по навчальному предметі з кодом 2003 на NULL, можна скористатися наступною командою:

UPDATE USP SET OCENKA - NULL WHERE PNUM = 2003;

Потужним засобом модифікації даних є використання подзапросов у команді модифікації UPDATE. Важливий принцип, якому треба дотримувати при роботі з командами модифікації і підзапитами, полягає в тому, що не можна в пропозиції FROM будь-якого подзапроса модифікувати таблицю, до якої посилається основна команда.

Зверніть увагу на наступний важливий момент - у команді модифікації UPDATE (до речі, так само, як і в команді INSERT) може виникнути проблематична ситуація, зв'язана з можливими дублікатами рядків, одержуваними в результаті вкладеного запиту. У цьому випадку, якщо в таблиці, що модифікується, є обмеження, що змушують її значення бути унікальними, команда чи модифікації вставки зазнає невдачі. Тому рекомендується яким-небудь образом з'ясувати те, що ці значення могли минулого вже бути використані в таблиці, перш ніж намагатися чи вставити модифікувати запис. Це можна реалізувати за допомогою додавання вкладеного подзапроса, що використовує в предикаті оператори EXISTS, IN, < > чи аналогічні.

Не варто забувати про заборону посилання вкладених запитах до таблиці, що модифікується командою UPDATE. З цієї причини запити і подзапросы в командах модифікації мають часом досить складну структуру. Крім того, усередині необов'язкового предиката цієї команди можна використовувати співвіднесені подзапросы, аналогічно тому як це робиться для DELETE.

Наприклад, що випливає запит збільшує розмір стипендії в 2 рази студентам, у яких маються оцінки, принаймні, по двох навчальних предметах:

UPDATE STUDENTS SET STIP=STIP*2

WHERE 2<=(SELECT COUNT (SNUM) FROM USP

WHERE STUDENTS.SNUM=USP.SNUM);

Тут внутрішній запит підраховує кількість записів у таблиці успішності для кожного студента, і, якщо воно 2 і більше, предикат основної функції стає щирим, а розмір стипендії модифікується.

Розглянемо ще один, досить складний, приклад зі співвіднесеним подзапросом. Тут будемо модифікувати розмір стипендії для студентів, що мають мінімальний бал у той інший день:

UPDATE STUDENTS SET STIP=STRIP-1WHERE SNUM IN

(SELECT SNUM FROM USP FIRST WHERE OCENKA =(SELECT MIN (OCENKA)

FROM USP SECOND WHERE FIRST.UDATE =SECOND. UDATE));

Як уже говорилося, до істотного недоліку UPDATE варто віднести неможливість послатися на таблицю, задіяну в будь-якому подзапросе з команди модифікації. Наприклад, неможливо однією командою виконати така дія, як модифікація оцінок для студентів, у яких оцінки нижче середньої. Для виконання цієї дії спочатку прийдеться виконати пошук середньої оцінки

SELECT AVG (OCENKA) FROM USP;

а потім результат цього запиту використовувати для модифікації

UPDATE USP SET OCENKA = OCENKA - 1 WHERE OCENKA < 4.2

Таким чином, команда UPDATE, що керує змістом запису, є однієї з ключових у мові SQL. Вона застосовна як до всіх рядків таблиці, якщо не використовується предикат, що визначає записи, що модифікуються, так і до конкретних рядків при наявності предиката, що, у свою чергу, може мати досить складну структуру.

Приклад використання оновлення даних

Зміна вулиці:

update abonent set street="kvitkova" where street="kvitneva";

Видалення записів, що повторюються (абонент, вулиця):

ALTER IGNORE TABLE abonent ADD UNIQUE INDEX(abon,street);

Видалення абонента «mm»

delete from abonent where abon="mm"

Вкладений запит. Дозволяє використати результат, що повертає одним запитом, в іншому запиті. Тому що результат повертає тільки оператор select, те як вкладений запит завжди виступає SELECT-запит. Як зовнішній запит може виступати запит за участю будь-якого SQL-оператора: select, insert, update, delete, create table й ін.

Пусть потрібно вивести назви й ціни товарних позицій з таблиці books для каталогу «Бази даних» таблиці catalogs:

Одержати аналогічний результат можна за допомогою многотабличного запиту, але є ряд завдань, які вирішуються тільки за допомогою вкладених запитів. Вкладений запит може застосовуватися не тільки з умовою WHERE, але й у конструкціях DISTINCT, GROUP BY, ORDER BY, LIMIT і т.д. Розрізняють:

· вкладені запити, що повертають одне значення;

· вкладені запити, що повертають кілька рядків.

У першому випадку вкладений запит повертає скалярне значення або литерал, що використається в зовнішньому запиті (підставляє результат на місце свого виконання). Наприклад, необхідно визначити назву каталогу, що містить найдорожчу товарну позицію:

Найбільше часто вкладені запити використаються в операціях порівняння в умовах, які задаються ключовими словами WHERE, HAVING або ON.

Однак наступний вкладений запит поверне помилку:

Щоб вибрати рядка з таблиці catalogs, у яких первинний ключ збігається з одним зі значень, що повертають вкладеним запитом, варто скористатися конструкцією IN:

Ключове слово ANY може застосовуватися з використанням будь-якого оператора порівняння. Використається логіка АБО, тобто досить, щоб спрацьовувало хоча б одне з багатьох умов. Запит виду WHERE X > ANY (SELECT Y …) можна інтерпретувати як «де X більше хоча б одного обраного Y». Відповідно, запит виду WHERE X < ANY (SELECT Y …) інтерпретується як «де X менше хоча б одного обраного Y». Розглянемо запит, що повертає імена й прізвища покупців, що зробили хоча б одну покупку:

Ключове слово ALL також може застосовуватися з використанням будь-якого оператора порівняння, але при цьому використається логіка И, тобто повинні спрацьовувати всі умови. Запит виду WHERE X > ALL (SELECT Y …) інтерпретується як «де X більше будь-якого обраного Y». Відповідно, запит виду WHERE X < ALL (SELECT Y …) інтерпретується як «де X менше, ніж всі обрані Y». Розглянемо запит, що повертає всі товарні позиції, ціна яких перевищує середню ціну кожного з каталогів:

Результуюча таблиця, що повертає вкладеним запитом, може не містити ні одного рядка. Для перевірки цього факту можуть використатися ключові слова EXISTS й NOT EXISTS.

Запит, що формує список покупців, що зробили хоча б одну покупку, можна записати в такий спосіб: