[DB] 데이터베이스 SQL DELETE FROM 구문 - 데이터 삭제하기

 

 

 

---

 

 

 

 

🔸 공식문서 DELETE FROM 구문🔸

-- Syntax for SQL Server and Azure SQL Database  
  
[ WITH <common_table_expression> [ ,...n ] ]  
DELETE   
    [ TOP ( expression ) [ PERCENT ] ]   
    [ FROM ]   
    { { table_alias  
      | <object>   
      | rowset_function_limited   
      [ WITH ( table_hint_limited [ ...n ] ) ] }   
      | @table_variable  
    }  
    [ <OUTPUT Clause> ]  
    [ FROM table_source [ ,...n ] ]   
    [ WHERE { <search_condition>   
            | { [ CURRENT OF   
                   { { [ GLOBAL ] cursor_name }   
                       | cursor_variable_name   
                   }   
                ]  
              }  
            }   
    ]   
    [ OPTION ( <Query Hint> [ ,...n ] ) ]   
[; ]  
  
<object> ::=  
{   
    [ server_name.database_name.schema_name.   
      | database_name. [ schema_name ] .   
      | schema_name.  
    ]  
    table_or_view_name   
}

 

-- Syntax for Azure Synapse Analytics and Microsoft Fabric

[ WITH <common_table_expression> [ ,...n ] ] 
DELETE [database_name . [ schema ] . | schema. ] table_name  
FROM [database_name . [ schema ] . | schema. ] table_name 
JOIN {<join_table_source>}[ ,...n ]  
ON <join_condition>
[ WHERE <search_condition> ]   
[ OPTION ( <query_options> [ ,...n ]  ) ]  
[; ]  

<join_table_source> ::=   
{  
    [ database_name . [ schema_name ] . | schema_name . ] table_or_view_name [ AS ] table_or_view_alias 
    [ <tablesample_clause>]  
    | derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]  
}

-- Syntax for Parallel Data Warehouse  
  
DELETE 
    [ FROM [database_name . [ schema ] . | schema. ] table_name ]   
    [ WHERE <search_condition> ]   
    [ OPTION ( <query_options> [ ,...n ]  ) ]  
[; ]

 

🔸 자주 쓰는 형태 🔸

DELETE FROM (테이블명) WHERE (조건);

 

 

=> 조건에 만족하는 칼럼 삭제

 

🔸 예시 구문 🔸

CREATE TABLE CLIENT (
ID VARCHAR(10) NOT NULL PRIMARY KEY,
CODE INT NOT NULL,
NAME VARCHAR(10) NOT NULL
);

 

 

 

 

INSERT INTO CLIENT (ID, CODE, NAME) VALUES ('id001', 1, '김멤버');
INSERT INTO CLIENT (ID, CODE, NAME) VALUES ('id002', 2, '이멤버');
INSERT INTO CLIENT (ID, CODE, NAME) VALUES ('id003', 2, '박멤버');
INSERT INTO CLIENT (ID, CODE, NAME) VALUES ('id004', 1, '최멤버');
INSERT INTO CLIENT (ID, CODE, NAME) VALUES ('id005', 3, '유멤버');
INSERT INTO CLIENT (ID, CODE, NAME) VALUES ('id006', 3, '권멤버');

 

 

DELETE FROM CLIENT
WHERE ID = 'id006' OR NAME LIKE '김%';

 

 

---