0
 45   5   1
  View Articles

Name  
   ¼Õº´¸ñ  (2002-04-22 06:12:56, Hit : 19719, Vote : 2261)
Homepage  
   http://www.itmembers.net
Subject  
   DML(Data Manipulation Language) - UPDATE
Áö±Ý±îÁö SELECT ¹®¿¡ ´ëÇØ Àå½Ã°£¿¡ °ÉÃÄ ½Ç½ÀÇØ º¸¾Ò½À´Ï´Ù.
À̹ø ½Ã°£ºÎÅÍ´Â UPDATE, INSERT, DELETE ¹®¿¡ ´ëÇØ Â÷·Ê´ë·Î ¾Ë¾Æ º¸°Ú½À´Ï´Ù.
UPDATE, INSERT, DELETE ¹®À» º¸Åë DML - Data Manipulation Language¶ó°í ÇÕ´Ï´Ù. ¿ì¸®¸»·Î µ¥ÀÌÅÍ Á¶ÀÛ¾î¶ó°í ¹ø¿ªÇÒ ¼ö ÀÖ½À´Ï´Ù.

SQL ¸í·É¾îÀÇ ºÐ·ù¿¡ ´ëÇØ¼­´Â ÀÌ¹Ì Á¦19°­ SQL°ú SQL*Plus¿¡¼­ ´Ù·é ÀûÀÌ ÀÖ½À´Ï´Ù. Ȥ½Ã ±â¾ïÀÌ ¾È³ª½Ã¸é ÂüÁ¶ÇϽñ⠹ٶø´Ï´Ù.

¿À´ÃÀº DML, ±× ù½Ã°£À¸·Î UPDATE ¸í·É¿¡ ´ëÇØ ¾Ë¾Æ º¸°Ú½À´Ï´Ù.


  1. UPDATE Çü½Ä°ú Ư¡

    UPDATE table_name
    SET column_one = expression, column_two = expression, ¡¦
    WHERE search condition(s);

    • UPDATE´Â Å×À̺íÀÇ Çà(·¹ÄÚµå) °ªÀ» ¡®º¯°æ¡¯ÇÒ ¶§ »ç¿ëÇÕ´Ï´Ù.
    • ÇϳªÀÇ UPDATE ¹®À¸·Î ÇϳªÀÇ Å×ÀÌºí ¶Ç´Â ºä(View)¸¸ ¼öÁ¤ÇÒ ¼ö ÀÖ½À´Ï´Ù.
      ¿¹¸¦ µé¾î ÇϳªÀÇ ºä(View)¶ó°í ÇØµµ ¿©·¯ °³ÀÇ Å×À̺í·ÎºÎÅÍ ¸¸µé¾îÁø ºä¶ó¸é UPDATE ¹®À» ¾µ ¼ö ¾ø½À´Ï´Ù.
    • SETÀý¿¡¼­ ¿øÇÏ´Â Ä÷³¿¡ ƯÁ¤ °ªÀ» ´ëÀÔÇÕ´Ï´Ù. À̶§ º¯°æÀ» ¿øÇÏ´Â Ä÷³¸¸ Ç¥½ÃÇÏ¸é µË´Ï´Ù.
    • Å×À̺íÀÇ ÇÁ¶óÀ̸Ӹ® Ű´Â UPDAGEÇÒ ¼ö ¾ø½À´Ï´Ù.
      ´Ü, ÇÁ¶óÀ̸Ӹ® ۶ó°í ÇØµµ ´Ù¸¥ Å×ÀÌºí¿¡¼­ FK(¿Ü·¡Å°)·Î ÂüÁ¶ÇÏÁö ¾ÊÀ¸¸é UPDATEÇÒ ¼ö ÀÖ½À´Ï´Ù.
    • WHERE¿¡¼­ Á¶°ÇÀ» ÁöÁ¤ÇÕ´Ï´Ù.
      ¸¸¾à WHEREÀýÀÌ ¾øÀ¸¸é Å×À̺íÀÇ ¸ðµç Çà(·¹ÄÚµå)¸¦ UPDATEÇÕ´Ï´Ù.


  2. ½Ç½ÀÀ» À§ÇÑ Å×ÀÌºí ¸¸µé±â

    À̹ø ½Ã°£ ½Ç½ÀÀ» À§ÇØ ´ÙÀ½°ú °°Àº CLASS Å×À̺í°ú CUSTOMER Å×À̺íÀ» ¸¸µé¾î µÓ½Ã´Ù.
    Å×À̺íÀ» ¸¸µé±â À§ÇÑ ¸í·É¾îÀÎ CREATE¿¡ ´ëÇØ¼­´Â ´ÙÀ½ ½Ã°£¿¡ ´Ù·ì´Ï´Ù. µåµð¾î!

    CREATE TABLE CLASS
    ( CODE CHAR(3) PRIMARY KEY,
      DESCRIPTION  VARCHAR2(30));
    INSERT INTO CLASS VALUES ('F01', 'FIRST');
    INSERT INTO CLASS VALUES ('B01', 'BUSINESS');
    INSERT INTO CLASS VALUES ('E01', 'ECONOMY');

    CREATE TABLE CUSTOMER
    ( CUST_NO CHAR(3) PRIMARY KEY,
      NAME VARCHAR2(10) NOT NULL,
      MILEAGE NUMBER,
      CODE CHAR(3),
      REG_DATE DATE NOT NULL);
    INSERT INTO CUSTOMER VALUES ('100', '±èö¼ö', 10000, 'F01', '01/01/01');
    INSERT INTO CUSTOMER VALUES ('101', 'ÀÌÀ±Á¤', 2000, 'E01', '01/03/05');
    INSERT INTO CUSTOMER VALUES ('102', '¹ÚÁؼ­', 5000, 'B01', '01/02/28');
    INSERT INTO CUSTOMER VALUES ('103', '±èÁ¾Çö', 12000, 'F01', '01/08/15');


  3. ½Ç½À 1

    ´ÙÀ½°ú °°Àº EMP Å×ÀÌºí¿¡¼­ EMP_NUMÀÌ 10001ÀÎ ·¹ÄÚµåÀÇ JOB_CODE¸¦ ¡°SS"·Î º¯°æÇØ º¾½Ã´Ù.



    UPDATE EMP
    SET JOB_CODE = 'SS'
    WHERE EMP_NUM = '10001';


  4. ½Ç½À 2

    ´ÙÀ½°ú °°Àº CUSTOMER Å×À̺í°ú CLASS Å×À̺íÀÌ ÀÖ´Ù°í ÇÒ ¶§,
    DESCRIPTIONÀÌ ¡°FIRST"ÀÎ CODE¿¡ ÇØ´çÇÏ´Â »ç¶÷ÀÇ ¸¶Àϸ®Áö¸¦ 50% Ãß°¡ÇØ º¾½Ã´Ù.
    µÎ °³ÀÇ Å×À̺íÀÌ ÀÖ°í, ¼­·Î ÂüÁ¶ÇØ¾ß Çϱ⠶§¹®¿¡ SubSelect ¹®À» »ç¿ëÇØ¾ß µÇ°ÚÁÒ?



    UPDATE CUSTOMER
    SET MILEAGE = MILEAGE * 1.5
    WHERE CODE = ( SELECT CODE
            FROM CLASS
            WHERE DESCRIPTION = 'FIRST');


ºñ±³Àû ½¬¿î ³»¿ëÀÔ´Ï´Ù.
½Ç½À 2¿¡¼­ ´Ù½Ã ³ª¿Â SubSelect ¹®Àº ½Ç¹«¿¡¼­ ¸Å¿ì À¯¿ëÇÏ°Ô »ç¿ëµË´Ï´Ù. ²À ¼÷ÁöÇϵµ·Ï ÇØ Áֽñ⠹ٶø´Ï´Ù.

ÀÌ»ó ¿ù¿äÀÏ ¾ÆÄ§, ¿¹»Û µ¿ÁÖÀÇ »¡¸® ³´±â¸¦ ºô¸ç
µ¿ÁÖ ¾Æºü ¼Õº´¸ñÀ̾ú½À´Ï´Ù.

Prev
   DML(Data Manipulation Language) - INSERT

¼Õº´¸ñ
Next
   ¿À¶óŬ µ¥ÀÌÅÍ °Ë»ö - SUBSELECT(3) EXIST èâ

¼Õº´¸ñ


Copyright 1999-2025 Zeroboard / skin by Zetyx