0

I am trying to fetch the some value from column 'data_sent' and then updating column 'registry' with this value. But getting the issues.

Query below:

update can_data 
set registry = (SELECT cast(SUBSTR(split_part(data_sent, 
'registry>', 2), 1, 26) as numeric) 
as registry FROM can_data);

SQL error:

ERROR: more than one row returned by a subquery used as an expression
********** Error **********

ERROR: more than one row returned by a subquery used as an expression SQL state: 21000

jarlh
  • 42,561
  • 8
  • 45
  • 63
sjain
  • 23,126
  • 28
  • 107
  • 185
  • your subquery returns more than one record. i don't know your data, but if those values are equal, a DISITNCT in the subquery might help, if not you have to think about what you really want to set as new registry value - TOP 1 might be a dirty solution – Esteban P. Jul 10 '17 at 11:38
  • Did you check https://stackoverflow.com/questions/21048955/postgres-error-more-than-one-row-returned-by-a-subquery-used-as-an-expression? It's kind of the same issue. – Nico Van Belle Jul 10 '17 at 11:40
  • It's generally a bad idea to store computed values like that. (Will lead to data inconsistency.) Create a view instead. – jarlh Jul 10 '17 at 11:41
  • yes I checked that solution but it doesn't apply here. I know that subquery is returning multiple rows but I need to apply all these rows to the outer query to update all rows. – sjain Jul 10 '17 at 11:42
  • why the (uncorrelated!) subquery? What is wrong with `update can_data set registry = cast(SUBSTR(split_part(data_sent, 'registry>', 2), 1, 26) as numeric);` ? – joop Jul 10 '17 at 11:44

1 Answers1

0

You don't need a subquery. All you need is this:

set registry = cast(SUBSTR(split_part(data_sent, 
'registry>', 2), 1, 26) as numeric
Dan Bracuk
  • 20,699
  • 4
  • 26
  • 43