CODEWITHSUNDEEP
sqlpostgresqlsubstringsubquery
JPT
JPT

Reputation: 65

Updating a table, setting a substring using another table via Sub-Select

I am currently using a query, however sometimes it doesn't update all fields. I need a workaround on this. Can anybody help me?

update table1 
set number = 
( 
select substring(number from offset) 
from table2 
where strpos(number,prefix) = '1' 
order by length(prefix) 
desc limit '1' 
) ;

table1:

number
----------
1001123456

table2:

prefix | offset
-------+-------
1001   | 5

My goal, hopefully, is to retrieve only the substring (in this example "123456" for every row in table1 that matches the specific prefix in table2.

Any help would be greatly appreciated! Thank you!

Upvotes: 0

Views: 535

Answers (1)

Mosty Mostacho
Mosty Mostacho

Reputation: 43464

Actually you don't need the offset field.

Table 1:

+------------+
|   NUMBER   |
+------------+
| 1001123456 |
|   10012222 |
|  200244444 |
|    2003666 |
+------------+

Table 2:

+--------+
| PREFIX |
+--------+
|   1001 |
|  20036 |
+--------+

Query:

select number, prefix,
  cast(
    substring(cast(t1.number as text), 
              char_length(cast(t2.prefix as text)) + 1)
  as integer) as suffix
from t1
join t2 on cast(t2.prefix as text) = 
  left(cast(t1.number as text),
       char_length(cast(t2.prefix as text)))

Result:

+------------+--------+--------+
|   NUMBER   | PREFIX | SUFFIX |
+------------+--------+--------+
| 1001123456 |   1001 | 123456 |
|   10012222 |   1001 |   2222 |
|    2003666 |  20036 |     66 |
+------------+--------+--------+

Upvotes: 1

Related Questions