-- Create a new database
CREATE DATABASE Teradata_DB
AS
    PERM = 20000000;  -- Adjust space as needed
 
-- Use the new database
DATABASE Teradata_DB;
 
-- Create the table
CREATE TABLE UPC_MLOGMV_ABRECHNUNGFF_NEW_UNIQUE (
    start_dt TIMESTAMP,
    some_data VARCHAR(100),
    UNIQUE_ID VARCHAR(50)
);
 
-- Insert test data into the table
INSERT INTO UPC_MLOGMV_ABRECHNUNGFF_NEW_UNIQUE (start_dt, some_data) VALUES ('2023-07-01 00:00:00', 'Data1');
INSERT INTO UPC_MLOGMV_ABRECHNUNGFF_NEW_UNIQUE (start_dt, some_data) VALUES ('2023-07-01 01:00:00', 'Data2');
INSERT INTO UPC_MLOGMV_ABRECHNUNGFF_NEW_UNIQUE (start_dt, some_data) VALUES ('2023-07-01 02:00:00', 'Data3');
INSERT INTO UPC_MLOGMV_ABRECHNUNGFF_NEW_UNIQUE (start_dt, some_data) VALUES ('2023-07-01 03:00:00', 'Data4');
INSERT INTO UPC_MLOGMV_ABRECHNUNGFF_NEW_UNIQUE (start_dt, some_data) VALUES ('2023-07-01 04:00:00', 'Data5');
INSERT INTO UPC_MLOGMV_ABRECHNUNGFF_NEW_UNIQUE (start_dt, some_data) VALUES ('2023-07-01 05:00:00', 'Data6');
INSERT INTO UPC_MLOGMV_ABRECHNUNGFF_NEW_UNIQUE (start_dt, some_data) VALUES ('2023-07-01 06:00:00', 'Data7');
INSERT INTO UPC_MLOGMV_ABRECHNUNGFF_NEW_UNIQUE (start_dt, some_data) VALUES ('2023-07-01 07:00:00', 'Data8');
INSERT INTO UPC_MLOGMV_ABRECHNUNGFF_NEW_UNIQUE (start_dt, some_data) VALUES ('2023-07-01 08:00:00', 'Data9');
INSERT INTO UPC_MLOGMV_ABRECHNUNGFF_NEW_UNIQUE (start_dt, some_data) VALUES ('2023-07-01 09:00:00', 'Data10');
 
-- Check the table data before the update
SELECT * FROM UPC_MLOGMV_ABRECHNUNGFF_NEW_UNIQUE;
 
-- Update the UNIQUE_ID column using a corrected query
UPDATE UPC_MLOGMV_ABRECHNUNGFF_NEW_UNIQUE devs
SET UNIQUE_ID = 'DT_' || (
    SELECT sequence_id
    FROM (
        SELECT 
            start_dt,
            (SUM(1) OVER (ORDER BY start_dt ROWS UNBOUNDED PRECEDING)) AS sequence_id
        FROM UPC_MLOGMV_ABRECHNUNGFF_NEW_UNIQUE sqq
    ) seq
    WHERE seq.start_dt = devs.start_dt
    AND seq.sequence_id < 10000
);
 
-- Check the table data after the update
SELECT * FROM UPC_MLOGMV_ABRECHNUNGFF_NEW_UNIQUE;
 

LS0gQ3JlYXRlIGEgbmV3IGRhdGFiYXNlCkNSRUFURSBEQVRBQkFTRSBUZXJhZGF0YV9EQgpBUwogICAgUEVSTSA9IDIwMDAwMDAwOyAgLS0gQWRqdXN0IHNwYWNlIGFzIG5lZWRlZAoKLS0gVXNlIHRoZSBuZXcgZGF0YWJhc2UKREFUQUJBU0UgVGVyYWRhdGFfREI7CgotLSBDcmVhdGUgdGhlIHRhYmxlCkNSRUFURSBUQUJMRSBVUENfTUxPR01WX0FCUkVDSE5VTkdGRl9ORVdfVU5JUVVFICgKICAgIHN0YXJ0X2R0IFRJTUVTVEFNUCwKICAgIHNvbWVfZGF0YSBWQVJDSEFSKDEwMCksCiAgICBVTklRVUVfSUQgVkFSQ0hBUig1MCkKKTsKCi0tIEluc2VydCB0ZXN0IGRhdGEgaW50byB0aGUgdGFibGUKSU5TRVJUIElOVE8gVVBDX01MT0dNVl9BQlJFQ0hOVU5HRkZfTkVXX1VOSVFVRSAoc3RhcnRfZHQsIHNvbWVfZGF0YSkgVkFMVUVTICgnMjAyMy0wNy0wMSAwMDowMDowMCcsICdEYXRhMScpOwpJTlNFUlQgSU5UTyBVUENfTUxPR01WX0FCUkVDSE5VTkdGRl9ORVdfVU5JUVVFIChzdGFydF9kdCwgc29tZV9kYXRhKSBWQUxVRVMgKCcyMDIzLTA3LTAxIDAxOjAwOjAwJywgJ0RhdGEyJyk7CklOU0VSVCBJTlRPIFVQQ19NTE9HTVZfQUJSRUNITlVOR0ZGX05FV19VTklRVUUgKHN0YXJ0X2R0LCBzb21lX2RhdGEpIFZBTFVFUyAoJzIwMjMtMDctMDEgMDI6MDA6MDAnLCAnRGF0YTMnKTsKSU5TRVJUIElOVE8gVVBDX01MT0dNVl9BQlJFQ0hOVU5HRkZfTkVXX1VOSVFVRSAoc3RhcnRfZHQsIHNvbWVfZGF0YSkgVkFMVUVTICgnMjAyMy0wNy0wMSAwMzowMDowMCcsICdEYXRhNCcpOwpJTlNFUlQgSU5UTyBVUENfTUxPR01WX0FCUkVDSE5VTkdGRl9ORVdfVU5JUVVFIChzdGFydF9kdCwgc29tZV9kYXRhKSBWQUxVRVMgKCcyMDIzLTA3LTAxIDA0OjAwOjAwJywgJ0RhdGE1Jyk7CklOU0VSVCBJTlRPIFVQQ19NTE9HTVZfQUJSRUNITlVOR0ZGX05FV19VTklRVUUgKHN0YXJ0X2R0LCBzb21lX2RhdGEpIFZBTFVFUyAoJzIwMjMtMDctMDEgMDU6MDA6MDAnLCAnRGF0YTYnKTsKSU5TRVJUIElOVE8gVVBDX01MT0dNVl9BQlJFQ0hOVU5HRkZfTkVXX1VOSVFVRSAoc3RhcnRfZHQsIHNvbWVfZGF0YSkgVkFMVUVTICgnMjAyMy0wNy0wMSAwNjowMDowMCcsICdEYXRhNycpOwpJTlNFUlQgSU5UTyBVUENfTUxPR01WX0FCUkVDSE5VTkdGRl9ORVdfVU5JUVVFIChzdGFydF9kdCwgc29tZV9kYXRhKSBWQUxVRVMgKCcyMDIzLTA3LTAxIDA3OjAwOjAwJywgJ0RhdGE4Jyk7CklOU0VSVCBJTlRPIFVQQ19NTE9HTVZfQUJSRUNITlVOR0ZGX05FV19VTklRVUUgKHN0YXJ0X2R0LCBzb21lX2RhdGEpIFZBTFVFUyAoJzIwMjMtMDctMDEgMDg6MDA6MDAnLCAnRGF0YTknKTsKSU5TRVJUIElOVE8gVVBDX01MT0dNVl9BQlJFQ0hOVU5HRkZfTkVXX1VOSVFVRSAoc3RhcnRfZHQsIHNvbWVfZGF0YSkgVkFMVUVTICgnMjAyMy0wNy0wMSAwOTowMDowMCcsICdEYXRhMTAnKTsKCi0tIENoZWNrIHRoZSB0YWJsZSBkYXRhIGJlZm9yZSB0aGUgdXBkYXRlClNFTEVDVCAqIEZST00gVVBDX01MT0dNVl9BQlJFQ0hOVU5HRkZfTkVXX1VOSVFVRTsKCi0tIFVwZGF0ZSB0aGUgVU5JUVVFX0lEIGNvbHVtbiB1c2luZyBhIGNvcnJlY3RlZCBxdWVyeQpVUERBVEUgVVBDX01MT0dNVl9BQlJFQ0hOVU5HRkZfTkVXX1VOSVFVRSBkZXZzClNFVCBVTklRVUVfSUQgPSAnRFRfJyB8fCAoCiAgICBTRUxFQ1Qgc2VxdWVuY2VfaWQKICAgIEZST00gKAogICAgICAgIFNFTEVDVCAKICAgICAgICAgICAgc3RhcnRfZHQsCiAgICAgICAgICAgIChTVU0oMSkgT1ZFUiAoT1JERVIgQlkgc3RhcnRfZHQgUk9XUyBVTkJPVU5ERUQgUFJFQ0VESU5HKSkgQVMgc2VxdWVuY2VfaWQKICAgICAgICBGUk9NIFVQQ19NTE9HTVZfQUJSRUNITlVOR0ZGX05FV19VTklRVUUgc3FxCiAgICApIHNlcQogICAgV0hFUkUgc2VxLnN0YXJ0X2R0ID0gZGV2cy5zdGFydF9kdAogICAgQU5EIHNlcS5zZXF1ZW5jZV9pZCA8IDEwMDAwCik7CgotLSBDaGVjayB0aGUgdGFibGUgZGF0YSBhZnRlciB0aGUgdXBkYXRlClNFTEVDVCAqIEZST00gVVBDX01MT0dNVl9BQlJFQ0hOVU5HRkZfTkVXX1VOSVFVRTsK