|











|
|
|
 |
0 |
|
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 ¸í·É¿¡ ´ëÇØ ¾Ë¾Æ º¸°Ú½À´Ï´Ù.

- 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ÇÕ´Ï´Ù.
- ½Ç½ÀÀ» À§ÇÑ Å×ÀÌºí ¸¸µé±â
À̹ø ½Ã°£ ½Ç½ÀÀ» À§ÇØ ´ÙÀ½°ú °°Àº 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');
- ½Ç½À 1
´ÙÀ½°ú °°Àº EMP Å×ÀÌºí¿¡¼ EMP_NUMÀÌ 10001ÀÎ ·¹ÄÚµåÀÇ JOB_CODE¸¦ ¡°SS"·Î º¯°æÇØ º¾½Ã´Ù.

UPDATE EMP
SET JOB_CODE = 'SS'
WHERE EMP_NUM = '10001';
- ½Ç½À 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 ¹®Àº ½Ç¹«¿¡¼ ¸Å¿ì À¯¿ëÇÏ°Ô »ç¿ëµË´Ï´Ù. ²À ¼÷ÁöÇϵµ·Ï ÇØ Áֽñ⠹ٶø´Ï´Ù.
ÀÌ»ó ¿ù¿äÀÏ ¾ÆÄ§, ¿¹»Û µ¿ÁÖÀÇ »¡¸® ³´±â¸¦ ºô¸ç
µ¿ÁÖ ¾Æºü ¼Õº´¸ñÀ̾ú½À´Ï´Ù.
|
 |
 |
|
|
|