Update columns in Column store table with billions of rows

Off-Topic
1

Hi ,

We have a column store table having billions of rows, we want to fix few of its columns to trim quotes.
Query we used is update tab_h set f=TRIM(BOTH ‘"’ FROM f)

Though it will not work for table with billion of rows. we even tried using this stored procedure
CREATE OR REPLACE PROCEDURE update_tab AS
DECLARE
BEGIN
FOR i in 1 … 50 LOOP
update tab_h set f=TRIM(BOTH ‘"’ FROM f) where f IN (SELECT f FROM tab_h WHERE f like ‘%"%"’ limit 10000);
end loop;
END

But this too doesn’t seems sufficient for updating billions of rows.

2

Did you try this?

update tab_h set f=TRIM(BOTH '"' FROM f) where f like '%"%'

That’s a lot easier and seems to do what you wanted. If that still doesn’t work then breaking it into batches like you described sounds like a good plan. Or if that still is a problem you could create a new table and do a “insert into … select from…” to transform the data, drop the old table, and rename the new one.