Database Tutorial

Course Tutorial Site

Site Admin

Help Section

without comments

Lab #6: SQL UPDATE Statement

Learn how to use ALTER TABLE and UPDATE statements to modify an existing table with data already inserted into the table.

Help Section

The following is an article that qualifies SQL join concepts, logic, implementation, and inheritance tree:

  • ALTER Statement
  • CHECK Constraint
  • NOT NULL Constraint
  • INSERT Statement
  • UPDATE Statement:

    Application Update of Data

    Instructional Example →

    There are two use cases when want to use an UPDATE statement to move data from one column in a table to another. The Instruction Example shows you how to implement the concept of migrating data from one column to another.

    Please perform the following tasks:

    There are two use cases when want to use an UPDATE statement to move data from one column in a table to another. They are:

    • Moving data from one column to a new column or set of new columns.
    • Updating related foreign key data to synchronize it with new rows in a table with modified data.

    Moving data from one column to another

    Instruction Details →

    This section shows you how to move data from one column to another column in the same row.

    The concept is best illustrated by creating a concrete example. You should build a menu table, like the following menu table and menu_s sequence:

    -- Create a menu table.
CREATE TABLE menu
( menu_id    NUMBER       CONSTRAINT pk_menu PRIMARY KEY
, menu_item  VARCHAR2(20) CONSTRAINT nn_menu NOT NULL);
 
-- Create the menu_s sequence.
CREATE SEQUENCE menu_s;

    You can then describe the table, like

    DESC menu

    It should show you:

    Name					   Null?    Type
----------------------------------------- -------- ----------------------------
MENU_ID				   NOT NULL NUMBER
MENU_ITEM				   NOT NULL VARCHAR2(20)

    You can populate the menu table with two rows to illustrate moving data, like these two INSERT statements:

    -- Insert one breakfast item.
INSERT INTO menu
( menu_id
, menu_item )
VALUES
( menu_s.NEXTVAL
,'Omlette' );
 
-- Insert one lunch item.
INSERT INTO menu
( menu_id
, menu_item )
VALUES
( menu_s.NEXTVAL
,'Hamburger' );
 
-- Make the change permanent.
COMMIT;

    You can query the data from the menu table, like

    SELECT * FROM menu;

    You should see:

       MENU_ID MENU_ITEM
---------- --------------------
	 1 Omlette
	 2 Hamburger

    After implementing your solution, the business decides its better to organize the data as breakfast, lunch, and dinner menu items. Items should appear in their respective categories or if appropriate in one or more breakfast, lunch, and dinner menu items. You initial testing requires that you add a breakfast_item and lunch_item column to the menu table. Then, you need to move the data item-by-item from the menu_item column to the appropriate breakfast_item or lunch_item column.

    You add the breakfast_item and lunch_item to the menu table with the ALTER TABLE statement:

    ALTER TABLE menu
  ADD ( breakfast_item   VARCHAR2(20))
  ADD ( lunch_item       VARCHAR2(20));

    You can then describe the table, like

    DESC menu

    It should show you:

    Name					   Null?    Type
----------------------------------------- -------- ----------------------------
MENU_ID	                                  NOT NULL NUMBER
MENU_ITEM                                 NOT NULL VARCHAR2(20)
BREAKFAST_ITEM                                     VARCHAR2(20)
LUNCH_ITEM                                         VARCHAR2(20)

    You could now move the data from the menu_item column to the breakfast_item and lunch_item columns with the following UPDATE statements:

    UPDATE menu
SET    breakfast_item = menu_item
WHERE  menu_item = 'Omlette';
 
UPDATE menu
SET    lunch_item = menu_item
WHERE  menu_item = 'Hamburger';

    You can query the data from the menu table, like

    SELECT * FROM menu;

    You should see:

    Name					   Null?    Type
   MENU_ID MENU_ITEM		BREAKFAST_ITEM	     LUNCH_ITEM
---------- -------------------- -------------------- --------------------
	 1 Omlette		Omlette
	 2 Hamburger				     Hamburger

    After shifting the data from the menu_item column to the breakfast_item and lunch_item columns, you can alter the menu table and add a CHECK constraint that ensures there is a not null value in either the breakfast_item or lunch_item column.

    The following adds a CHECK constraint that guarantees the behavior of either a value in the breakfast_item or lunch_item column:

    ALTER TABLE menu
  ADD CONSTRAINT ck_menu
      CHECK (breakfast_item IS NOT NULL OR lunch_item IS NOT NULL);

    Moving data from one column to another while updating foreign key values

    Instruction Details →

    This section shows you how to move data from one column to another column in the same row, how to add new data to support a changing business rule, and how to update obsolete or incorrect foreign key values.

    The prior concrete example showed you how to move data from one column to two different columns, however, it didn’t show you how to update dependent foreign key in another table. You will build on the menu table example to show you how to move data from one column to another and update dependent foreign key values in another table.

    The first step requires that you undo the UPDATE statements that moved the data originally. You would undo the prior UPDATE statements by issuing a ROLLBACK statement, but you applied a CHECK constraint against the table that automatically committed any data changes since your last commit.

    You drop the CHECK constraint first and then use an UPDATE statement to replace the string values in the breakfast_item or lunch_item columns with null values.

    ALTER TABLE menu
  DROP CONSTRAINT ck_menu;

    After undoing the update of the menu_item column value to the breakfast_item and lunch_item columns, you create a dependent PAYMENT table, like this:

    -- Create a payment table.
CREATE TABLE payment
( payment_id      NUMBER        CONSTRAINT pk_payment PRIMARY KEY
, menu_id         NUMBER  
, meal            VARCHAR2(20)  CONSTRAINT nn_payment1 NOT NULL
, payment_amount  NUMBER(15,2)  CONSTRAINT nn_payment2 NOT NULL
, payment_date    DATE          CONSTRAINT nn_payment3 NOT NULL
, CONSTRAINT fk_payment FOREIGN KEY(menu_id) REFERENCES menu(menu_id));
 
-- Create a payment_s sequence.
CREATE SEQUENCE payment_s;

    You can describe the table with the following syntax:

    DESC payment

    It will show:

    Name					   Null?    Type
----------------------------------------- -------- ----------------------------
PAYMENT_ID                                NOT NULL NUMBER
MENU_ID	                                           NUMBER
MEAL                                      NOT NULL VARCHAR2(20)
ITEM                                      NOT NULL VARCHAR2(20)
PAYMENT_AMOUNT                            NOT NULL NUMBER(15,2)
PAYMENT_DATE                              NOT NULL DATE

    You can insert three rows into the payment table, using the following pattern of INSERT statements. You should note that your payment table’s business rule to discover a foreign key menu_id value is based solely on the string value of the menu_item column.

    INSERT INTO payment
( payment_id
, menu_id
, meal
, item
, payment_amount
, payment_date )
VALUES
( payment_s.NEXTVAL
,(SELECT menu_id
  FROM   menu
  WHERE  menu_item = 'Omlette')
,'Breakfast'
,'Omlette'
, 4.99
, SYSDATE );
 
INSERT INTO payment
( payment_id
, menu_id
, meal
, item
, payment_amount
, payment_date )
VALUES
( payment_s.NEXTVAL
,(SELECT menu_id
  FROM   menu
  WHERE  menu_item = 'Hamburger')
,'Lunch'
,'Hamburger'
, 5.99
, SYSDATE );
 
INSERT INTO payment
( payment_id
, menu_id
, meal
, item
, payment_amount
, payment_date )
VALUES
( payment_s.NEXTVAL
,(SELECT menu_id
  FROM   menu
  WHERE  menu_item = 'Hamburger')
,'Dinner'
,'Hamburger'
, 5.99
, SYSDATE );

    You can query the data from the menu table, like

    COLUMN payment_id      FORMAT 9999      HEADING "Pmt|ID #"
COLUMN menu_id         FORMAT 9999      HEADING "Menu|ID #"
COLUMN meal            FORMAT A12       HEADING "Meal|Description"
COLUMN item            FORMAT A12       HEADING "Item|Description"
COLUMN payment_amount  FORMAT 9,999.00  HEADING "Payment|Amount"
COLUMN payment_date    FORMAT A11       HEADING "Payment|Date"
SELECT * FROM payment;

    You should see:

      Pmt  Menu Meal	 Item		Payment Payment
 ID #  ID # Description  Description	 Amount Date
----- ----- ------------ ------------ --------- -----------
    1	  1 Breakfast	 Omlette	   4.99 16-OCT-18
    2	  2 Lunch	 Hamburger	   5.99 16-OCT-18
    3	  2 Dinner	 Hamburger	   5.99 16-OCT-18

    The payment table actually implements a business rule that each row must map to a meal and a menu item. The restaurant recognizes only three meals – breakfast, lunch, and dinner. You realize that you must change the menu table.

    You need to add a dinner_item column to the menu table with the ALTER TABLE statement. This statement adds the dinner_item column to the table.

    ALTER TABLE menu
  ADD ( dinner_item  VARCHAR2(20));

    You can then describe the table, like

    DESC menu

    It should show you:

    Name					   Null?    Type
----------------------------------------- -------- ----------------------------
MENU_ID	                                  NOT NULL NUMBER
MENU_ITEM                                 NOT NULL VARCHAR2(20)
BREAKFAST_ITEM 	                                   VARCHAR2(20)
LUNCH_ITEM                                         VARCHAR2(20)
DINNER_ITEM                                        VARCHAR2(20)

    You can move the data from the menu_item column to the breakfast_item and lunch_item columns like you did before:

    UPDATE menu
SET    breakfast_item = menu_item
WHERE  menu_item = 'Omlette';
 
UPDATE menu
SET    lunch_item = menu_item
WHERE  menu_item = 'Hamburger';

    You can’t modify a CHECK constraint in an Oracle database. You must drop and recreate a CHECK constraint.

    After ensuring there’s a value in either the breakfast_item, lunch_item, or dinner_item column for each row, you can create a new CHECK constraint. The following drops the old check constraint and creates a new one that validates that a breakfast_item, lunch_item, or dinner_item is not null in every row.

    -- Drop the existing constraint.
ALTER TABLE menu
  DROP CONSTRAINT ck_menu;
 
-- Create the new constraint.
ALTER TABLE menu
  ADD CONSTRAINT ck_menu
      CHECK (breakfast_item IS NOT NULL OR
             lunch_item IS NOT NULL OR
             dinner_item IS NOT NULL);

    Now that we

    The “Hamburger” value that existed in theALTER TABLE menu

    You could now move the data from the menu_item column to the breakfast_item and lunch_item columns with the following UPDATE statements:

    UPDATE menu
SET    breakfast_item = menu_item
WHERE  menu_item = 'Omlette';
 
UPDATE menu
SET    lunch_item = menu_item
WHERE  menu_item = 'Hamburger';

    You can query the data from the menu table, like

    SELECT * FROM menu;

    You should see:

       MENU_ID MENU_ITEM		BREAKFAST_ITEM	     LUNCH_ITEM
---------- -------------------- -------------------- --------------------
	 1 Omlette		Omlette
	 2 Hamburger				     Hamburger

    At this point, an inspection of the data in the payment table would show you that there was a dinner payment for a Hamburger. That means the Hamburger belongs in two rows. One row where Hamburger is in the lunch_item column and another row where Hamburger is in the dinner_item column.

    You can insert the new row with an INSERT statement that queries the row where Hamburger appears in the lunch_item column. The following inserts the new row:

    INSERT INTO menu
( menu_id
, dinner_item )
SELECT   menu_s.NEXTVAL
,        menu_item
FROM     menu
WHERE    menu_item = 'Hamburger';

    You can now query the data set of the menu table:

    COLUMN menu_id         FORMAT 9999  HEADING "Menu|ID #"
COLUMN breakfast_item  FORMAT A10  HEADING "Breakfast|Item"
COLUMN lunch_item      FORMAT A10  HEADING "Lunch|Item"
COLUMN dinner_item     FORMAT A10  HEADING "Dinner|Item"
SELECT   menu_id
,        breakfast_item
,        lunch_item
,        dinner_item
FROM     menu;

    It displays:

     Menu Breakfast  Lunch	    Dinner
 ID # Item	 Item	    Item
----- ---------- ---------- ----------
    1 Omlette
    2		 Hamburger
    3			    Hamburger

    A join between the menu and payment tables no longer returns the proper data set because two rows holding foreign key values in the payment table point to one row in the menu table. That occurs because you changed the business rule after inserting data.

    A LEFT JOIN illustrates the mismatch between the two tables. The mismatch occurs because you changed data in the menu table after adding dependent data in the payment table.

    COLUMN menu_id         FORMAT 9999      HEADING "Menu|ID #"
COLUMN breakfast_item  FORMAT A10       HEADING "Breakfast|Item"
COLUMN lunch_item      FORMAT A10       HEADING "Lunch|Item"
COLUMN dinner_item     FORMAT A10       HEADING "Dinner|Item"
COLUMN menu_id         FORMAT 9999      HEADING "Pmt|ID #"
COLUMN menu_id         FORMAT 9999      HEADING "Menu|ID #"
COLUMN meal            FORMAT A12       HEADING "Meal|Description"
COLUMN payment_amount  FORMAT 9,999.00  HEADING "Payment|Amount"
SELECT   m.menu_id
,        m.breakfast_item
,        m.lunch_item
,        m.dinner_item
,        p.payment_id
,        p.menu_id
,        p.meal
,        p.payment_amount
FROM     menu m LEFT JOIN payment p
ON       m.menu_id = p.menu_id;

    It returns the following output:

     Menu Breakfast  Lunch	    Dinner	 Pmt  Menu Meal 	  Payment
 ID # Item	 Item	    Item	ID #  ID # Description	   Amount
----- ---------- ---------- ---------- ----- ----- ------------ ---------
    1 Omlette				   1	 1 Breakfast	     4.99
    2		 Hamburger		   2	 2 Lunch	     5.99
    2		 Hamburger		   3	 2 Dinner	     5.99
    3			    Hamburger

    The output shows that the two rows point to the lunch time Hamburger when the meal descriptions indicate one should point to the lunch time Hamburger row and the other should point to the dinner time Hamburger row. You can fix the data by updating the now incorrect foreign key value in the payment table’s menu_id column.

    The first step to repair the problem requires finding a query that maps the values because in the real world you may have thousands or millions of rows to correct. The query to find the incorrect foreign key values in the payment table’s menu_id column is:

    SELECT   m.menu_id
,        p.payment_id
,        p.menu_id
FROM     menu m join payment p
ON       p.meal = 'Breakfast' AND m.breakfast_item = p.item
OR       p.meal = 'Lunch' AND m.lunch_item = p.item
OR       p.meal = 'Dinner' AND m.dinner_item = p.item;

    It returns the following:

     Menu	Pmt  Menu
 ID #  ID #  ID #
----- ----- -----
    1	  1	1
    2	  2	2
    3	  3	2

    You can refactor the query into an UPDATE statement. The following refactored UPDATE statement fixes the mismatch. It sets the payment table’s menu_id column value equal to the correct menu table’s menu_id value. Moreover, it implements the now current business rule.

    UPDATE   payment p
SET      p.menu_id = (SELECT   m.menu_id
                      FROM     menu m
                      WHERE    p.meal = 'Breakfast' AND m.breakfast_item = p.item
                      OR       p.meal = 'Lunch' AND m.lunch_item = p.item
                      OR       p.meal = 'Dinner' AND m.dinner_item = p.item);

    While a query looking for the data to correct is typically done in the WHERE clause of an UPDATE statement and a query looking for the data to replace is typically done in the SET clause, the problem requires a correlated UPDATE statement because of the either breakfast_item, lunch_item, or dinner_item columns of the menu table. The WHERE clause of the correlated subquery maps the meal column value to the proper columns before checking for a match between those columns and the payment table’s item column value.

    A final require of the data with a LEFT JOIN shows that all data connects correctly:

    COLUMN menu_id         FORMAT 9999      HEADING "Menu|ID #"
COLUMN breakfast_item  FORMAT A10       HEADING "Breakfast|Item"
COLUMN lunch_item      FORMAT A10       HEADING "Lunch|Item"
COLUMN dinner_item     FORMAT A10       HEADING "Dinner|Item"
COLUMN payment_id      FORMAT 9999      HEADING "Pmt|ID #"
COLUMN menu_id         FORMAT 9999      HEADING "Menu|ID #"
COLUMN meal            FORMAT A12       HEADING "Meal|Description"
COLUMN payment_amount  FORMAT 9,999.00  HEADING "Payment|Amount"
SELECT   m.menu_id
,        m.breakfast_item
,        m.lunch_item
,        m.dinner_item
,        p.payment_id
,        p.menu_id
,        p.meal
,        p.payment_amount
FROM     menu m LEFT JOIN payment p
ON       m.menu_id = p.menu_id;

    The results show the proper correction:

     Menu Breakfast  Lunch	    Dinner	 Pmt  Menu Meal 	  Payment
 ID # Item	 Item	    Item	ID #  ID # Description	   Amount
----- ---------- ---------- ---------- ----- ----- ------------ ---------
    1 Omlette				   1	 1 Breakfast	     4.99
    2		 Hamburger		   2	 2 Lunch	     5.99
    3			    Hamburger	   3	 3 Dinner	     5.99

    You have completed a concrete example of changing the structure of the database and the mechanism to fix disconnected foreign key column values.

Written by michaelmclaughlin

August 13th, 2018 at 11:21 am

Posted in