How to UPDATE and INSERT into a table using ONE single statement?

 In Oracle PL/SQL, you can use the MERGE statement to perform both updates and inserts in a single operation. This is particularly useful when you want to synchronize data between two tables based on certain conditions. The MERGE statement essentially combines the functionality of UPDATE and INSERT into a single command. Here's the basic syntax:

MERGE INTO target_table 
USING source_table 
ON (condition) 
WHEN MATCHED THEN 
    UPDATE SET column1 = value1, column2 = value2 
WHEN NOT MATCHED THEN 
    INSERT (column1, column2) VALUES (value1, value2);

Let's break down the components:

• target_table: The table you want to update or insert data into.

• source_table: The table from which you're getting the data.

• condition: The condition that determines whether a match is found between the target_table and the source_table.

• WHEN MATCHED THEN UPDATE: Specifies the action to take when a match is found. You define the columns to update and their new values.

• WHEN NOT MATCHED THEN INSERT: Specifies the action to take when no match is found. You define the columns to insert data into and their corresponding values.

Here's a simple example:

Assume you have a employees table and a employees_updates table. You want to update existing employee records based on their employee_id, and if the employee doesn't exist in the main table, you want to insert the data as a new record.

MERGE INTO employees e 
USING employees_updates u 
ON (e.employee_id = u.employee_id) 
WHEN MATCHED THEN 
    UPDATE SET e.first_name = u.first_name, e.last_name = u.last_name 
WHEN NOT MATCHED THEN 
    INSERT (employee_id, first_name, last_name)
    VALUES (u.employee_id,                 u.first_name, u.last_name);

In this example, if an employee in the employees_updates table matches an employee in the employees table based on their employee_id, the UPDATE clause will be executed. If there's no match, the INSERT clause will be executed.

Using the MERGE statement is a powerful way to efficiently manage updates and inserts in a single command, reducing the need for multiple SQL statements.

Difference between TRUNCATE table and DELETE table

 TRUNCATE TABLE and DELETE are two SQL statements used to remove data from a table in a database. However, they differ in terms of their functionality, speed, and impact. Here's a breakdown of the differences between TRUNCATE TABLE and DELETE:

1. TRUNCATE TABLE:

• Functionality: TRUNCATE TABLE is used to remove all the data from a table while keeping the structure intact. It effectively resets the table, making it empty.
• Speed: TRUNCATE TABLE is generally faster than DELETE because it's a minimally logged operation. It removes data in large blocks and deallocates space, resulting in less overhead.
• Rollback: Unlike DELETE, TRUNCATE TABLE cannot be rolled back. Once data is truncated, it cannot be recovered using a rollback operation.
• Triggers: TRUNCATE TABLE does not activate delete triggers associated with the table.
• Restrictions: It's important to note that you cannot use TRUNCATE TABLE on a table that is referenced by a foreign key constraint or participates in an indexed view.
• In Memory: Truncate is a DDL statement. It also involves two commits, one before the statement execution and one after, meaning that after truncating a table, the data cannot be recovered. Due to this, truncate recovers space from memory.

Example:

sql
TRUNCATE TABLE employees;

2. DELETE:

• Functionality: DELETE is used to remove specific rows from a table based on a specified condition.
• Speed: Compared to TRUNCATE TABLE, DELETE can be slower, especially when deleting a large number of rows. Each row is logged individually, leading to more overhead.
• Rollback: A DELETE operation can be rolled back, allowing you to recover data if necessary.
• Triggers: DELETE activates delete triggers associated with the table, providing more control over the deletion process.
• Foreign Keys: When using DELETE, you need to handle foreign key constraints. Depending on how foreign key relationships are defined, you might need to delete related rows from other tables before deleting from the main table.
• In Memory: Delete is DML statement, merely data manipulation. After executing a delete statement, we can do a Rollback to get the data back, or do a commit. The delete does not recover memory.

Example:

sql
DELETE FROM employees WHERE department_id = 10;

In Summary:

• Use TRUNCATE TABLE when you want to quickly remove all data from a table and reset its structure. It's faster but less flexible compared to DELETE.
• Use DELETE when you want to remove specific rows from a table based on a condition. It provides more control, triggers activation, and allows rollback.

The choice between TRUNCATE TABLE and DELETE depends on your specific needs. If you need to quickly remove all data from a table, and you don't need the option to roll back, TRUNCATE TABLE is generally the better choice. If you need more control over the deletion process and want the option to roll back, use DELETE.