All Forums Tools
Pauly 2 posts Joined 09/15
29 Sep 2015
BTEQ DELETE STATEMENT Successfull - nothing deleted in table

Hi...
Quite new to BTEQ - but 5 years experience in SQL assistant.
Question is if there are default restrictions to "write" from BTEQ into a DB-table.
Now, in BTEQ I'm succesfull to logon and run the SQL...

Teradata BTEQ 14.00.00.07 for WIN32. Enter your logon or BTEQ command:
.logon TD_KEITH/MINGELS
.logon TD_KEITH/MINGELS
Password: 
********

 *** Logon successfully completed.
 *** Teradata Database Release is 14.00.07.16                   
 *** Teradata Database Version is 14.00.07.15                     
 *** Transaction Semantics are ANSI.
 *** Session Character Set Name is 'ASCII'.
 
 *** Total elapsed time was 1 second.
 
 BTEQ -- Enter your SQL request or BTEQ command: 
DEL FROM t02_opdm.test_bu
;
DEL FROM t02_opdm.test_bu
;

 *** Delete completed. 6 rows removed. 
 *** Total elapsed time was 1 second.


 BTEQ -- Enter your SQL request or BTEQ command: 
.logoff

Afterwards I looked in the resultset of the table via SQL Assistant and the data is still there!?
What is going wrong??
(Running a procedure gives the same result (of course))
Thanks in advance...
Pauly

dnoeth 4628 posts Joined 11/04
29 Sep 2015

You logged on using an ANSI session (your system default) and didn't COMMIT before the LOGOFF -> DELETE is rolled back.
Your connection from SQL Assistant is probably explicitly set to a Teradata mode session, where each request is a single transaction by default. 
So either do a .SET SESSION TRANSACTION BTET; before the logon or start using COMMIT;

Dieter

Pauly 2 posts Joined 09/15
29 Sep 2015

Wow...
Thanks for the prompt reply!!
It's working now...
Thanks Dieter

You must sign in to leave a comment.