Truncate with Condition

Truncate with condition

No, TRUNCATE is all or nothing. You can do a DELETE FROM <table> WHERE <conditions> but this loses the speed advantages of TRUNCATE.

Can we use truncate with where clause?

No, TRUNCATE is all or nothing. You can do a DELETE FROM <table>
WHERE <conditions> but this loses the speed advantages of TRUNCATE.

This thread is a good read :)

Does TRUNCATE and DELETE produce same results?

FALSE — I'll explain in the context of MySQL's InnoDB engine. You tagged the question both mysql and oracle11g.

DELETE

• DELETE can be part of a transaction. The action can be rolled back. It copies old records to the rollback segment, and if you commit then those record versions are garbage-collected. If you rollback, the record versions are restored to the table.

• DELETE does not reset the AUTO_INCREMENT counter for the table.

• DELETE does not create a new tablespace. The size of the tablespace does not shrink. So it won't reduce the footprint of the table on disk, it will only mark most of the pages in the tablspace as "free", i.e. may be overwritten by subsequent data.

• DELETE executes delete triggers for each row affected.

• DELETE requires the DELETE privilege.

• You can do a DELETE with a JOIN clause, so you can optionally delete from multiple tables in one statement.

• If you use binary logs to record the changes and you use binlog_format=ROW, the binary log will fill with as many row images as the number of rows deleted. This could take a lot of space.

TRUNCATE TABLE

• TRUNCATE TABLE is a DDL statement, as you said, so it performs an implicit commit.

• TRUNCATE TABLE creates a new tablespace with minimum size, and drops the original tablespace.

• TRUNCATE TABLE resets the AUTO_INCREMENT for the respective table.

• TRUNCATE TABLE does not copy any records to the rollback segment.

• TRUNCATE TABLE does not execute any triggers.

• TRUNCATE TABLE requires the DROP privilege.

• You can only truncate one table per statement.

• TRUNCATE TABLE will not take more space in the log for a large table. DDL statements are always written to the binary log in statement format, even if you set binlog_format=ROW.

• TRUNCATE TABLE is basically equivalent to this sequence:

    CREATE TABLE new_table LIKE original_table; -- i.e. with zero rows
    RENAME TABLE original_table TO table_to_drop, new_table TO original_table;
    DROP TABLE table_to_drop;
  

Truncate entries from a string column of pandas dataframe based on a condition

lets try explode

s = df['b'].str.split(',').explode().astype(int)

df['b'] = s[s.isin(lst)].astype(str).groupby(level=0).agg(", ".join)

---

print(df)

   a     b
0  1  2, 4
1  2   245

Pre 0.25 Pandas Solution using stack

s = df['b'].str.split(',',expand=True).stack().astype(int)
df['b'] = s[s.isin(lst)].astype(str).groupby(level=0).agg(", ".join)

TRUNCATE whole table if TO_CHAR(SYSDATE,'DD')='01' in SQL

You can use PL/SQL as follows:

BEGIN
IF TRUNC(SYSDATE) = TRUNC(SYSDATE,'MON') THEN
EXECUTE IMMEDIATE 'TRUNCATE TABLE YOUR_TABLE';
END IF;
END;
/

How to write an SQL statement that runs only if condition is fulfilled?

you can try to do it that way. i didn't test this solution, but it should give you an idea how you could do it

CREATE OR REPLACE PROCEDURE proc_copy_records_and_then_delete
IS
  v_row_count number;
BEGIN
  insert into AUDIT_USER.AUDIT_HISTORY_TABLE 
   select * from SYS.AUD$; 
  v_row_count := SQL%ROWCOUNT ;
  IF v_row_count > 0 THEN -- check if some rows where inserted
    INSERT INTO LOG_TABLE (TEXT) VALUES (v_row_count); 
    COMMIT;
    execute immediate 'truncate table SYS.AUD$';

  end if;
end;
/

Redshift: TRUNCATE TABLE IF EXISTS

You have two options to achieve it:

SQL Procedure/Script
Using IF condition, checking if table exists, then only truncate your table.

With Plain SQL Statements
Use Create table with if not exists in combination with Truncate, this will ensure table always exists & your consecutive SQL statements don't error out & stop.

CREATE TABLE @tobetruncated IF NOT EXISTS
TRUNCATE TABLE @tobetruncated

NOTE: This is not specific to REDSHFIT, mostly applies to all DB unless it supports special functions (like one I know Oracle has TABLE_EXISTS_ACTION). Truncate is like a all or nothing operation, and thats what makes it much better in performance than DELETE.

---

Related Topics