Mysql: How to INSERT, UPDATE or DELETE record in one statement

Generally we write separate statements to INSERT, UPDATE or DELETE data based on certain conditions. But now in SQL Server 2008 there is a new feature present called MERGE statement using which we can INSERT, UPDATE or DELETE record in one statement.

MERGE is a new feature which Microsoft has introduced with SQL Server 2008 that provides an efficient way to perform multiple DML operations. In previous version of SQL server, we had to write separate statements to INSERT, UPDATE or DELETE data based on certain conditions, but now using MERGE statement we can include the logic of such data modifications in one statement that even checks when the data is matched then just update it and when unmatched then insert it. One of the most imortant advantage of MERGE statement is all the data is read and processed only once.The MERGE statement internally works as an individual insert, update and delete statement within a single Merge statement.You need to specify the SOURCE and the TARGET table or query which should be joined together.

EXAMPLE: MERGE WITH INSERT AND UPDATE

CREATE PROCEDURE MERGE_INSERT_UPDATE
   @USERNAME VARCHAR(50),
   @ROOM VARCHAR(20)
AS 
BEGIN
    SET NOCOUNT ON;
           
    MERGE USERS AS target
    USING (SELECT @USERNAME, @ROOM) AS source (USERNAME, ROOM)
    ON (target.USERNAME = source.USERNAME)
    WHEN MATCHED THEN 
        UPDATE SET ROOM = source.ROOM
 WHEN NOT MATCHED THEN 
     INSERT (USERNAME, ROOM)
     VALUES (source.USERNAME, source.ROOM)
     OUTPUT deleted.*, $action, inserted.* ;
END;
GO
 
EXEC MERGE_INSERT_UPDATE @USERNAME = 'Charlie bB', @ROOM = 'New ROOM';

MERGE WITH DELETE AND UPDATE

CREATE PROCEDURE MERGE_WITH_UPDATE_DELETE
   
AS
MERGE Rooms AS target
USING (SELECT Users.ID,Users.RENT FROM Users
    JOIN Rooms 
    ON Users.ID = Rooms.ID
    ) AS source (ID,RENT)
ON (target.ID = source.ID)
WHEN MATCHED AND target.RENT <= 450
    THEN DELETE
WHEN MATCHED 
    THEN UPDATE SET target.RENT = 5000
 
OUTPUT $action, Inserted.ID, Inserted.RENT, Deleted.ID,
    Deleted.RENT;
GO
 
EXEC MERGE_WITH_UPDATE_DELETE ;