CODEWITHSUNDEEP
sqlpostgresqlamazon-redshift
Gyanendra Singh
Gyanendra Singh

Reputation: 1005

Update redshift column value with modified data from other column

I have a redshift table which is used for tracking, and as a result its pretty huge. I need to update one column after applying some text operations and extracting a value from another column.

The query that I have managed to write works only for one row.

UPDATE schema.table_name SET data_id = (SELECT split_part(regexp_substr(data_column,'pattern=[^&]*'),'=',2)::BIGINT FROM schema.table_name where id = 1620) WHERE id = 1620;

How do I get it to work for every row in the table.

Upvotes: 0

Views: 3237

Answers (3)

Pop
Pop

Reputation: 4022

Updates are not efficient in Redshift. If you have a huge table and you intend to update every single row, you should instead copy the data (with the updated column) to a new table and then flip them.

Upvotes: 0

Kouber Saparev
Kouber Saparev

Reputation: 8105

UPDATE
  schema.table_name
SET
  data_id = SPLIT_PART(REGEXP_SUBSTR(data_column, 'pattern=[^&]*'),'=',2)::BIGINT;

Upvotes: 2

Paweł Tajs
Paweł Tajs

Reputation: 482

Just don't put WHERE id = 1620; at end of update query.

Upvotes: 0

Related Questions