CODEWITHSUNDEEP
sqloracle-databasesql-update
Imran Hemani
Imran Hemani

Reputation: 629

Oracle: Update based on longest value

I have a table with following columns:

**ITEM LOCATION COUNTRY DESCRIPTION**
100    KS1      SA     Apple iPhone 13 - 64GB
200    KS2      SA     Apple iPhone 13 - 64GB International Warranty
500    KS3      SA     Apple iPhone 13 - with Warranty
600    KS4      SA     Apple iPhone 13
300    KS1      BH     Apple iPhone 12 - 64GB
400    KS2      BH     Apple iPhone 12 - 64GB International Warranty

I want to update 100 and 200 with: Apple iPhone 13 - 64GB International Warranty based on longest description and country and same for 300 and 400 as: Apple iPhone 12 - 64GB International Warranty

How can I update Item/Country Description based on longest description of item/country

Upvotes: 0

Views: 41

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 95072

You can use Oracle's KEEP LAST:

update mytable t
set description = 
(
  select max(t2.description) keep (dense_rank last order by length(t2.description))
  from mytable t2
  where t2.country = t.country
);

Or stick to standard SQL:

update mytable t
set description = 
(
  select t2.description
  from mytable t2
  where t2.country = t.country
  order by length(t2.description) desc
  fetch first row only
);

(It seems weird that in your table there can only be one item per country and that the same item has multiple item numbers, but I guess that is just because you have tried to create simplified sample data for us, and your real data is quite different.)

Upvotes: 1

Related Questions