Forum Stats

  • 3,728,425 Users
  • 2,245,615 Discussions
  • 7,853,521 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Log In Register

Categories

update taking more time -- how to improve performance or how to overwrite

user13115886
user13115886 Member Posts: 116 Blue Ribbon
edited October 2020 in SQL & PL/SQL

Hi,

Trying to take first two characters from Quote_Nbr and updating country in the same table.

Please advise.


UPDATE QUOTES_TABL a

SET a.Country = (SELECT SUBSTR (b.QUOTE_NBR, 2, 2)

FROM QUOTES_TABL b WHERE b.SOURCE = 'XX')

WHERE a.SOURCE = 'XX' AND a.QUOTE_NBR = b.QUOTE_NBR;


Thanks

AK

· Share on TwitterShare on Facebook

Answers

  • Jason_(A_Non)
    Jason_(A_Non) Member Posts: 2,009 Silver Trophy

    Why can't you just do?

    UPDATE quotes_tabl
SET country = SUBSTR(quote_nbr, 2, 2)
WHERE SOURCE = 'XX';

    Is there a valid business reason to store a subset of data from one column as a separate column? You could always make country a VIRTUAL column instead.

    · Share on TwitterShare on Facebook
  • mathguy
    mathguy Member Posts: 9,728 Gold Crown

    Your code has conditions like SOURCE = 'XX' but you didn't explain what role that plays in your problem (when you explained the problem in words). I suspect that means something, but I am not sure what.

    As an aside, SUBSTR(something, 2, 2) does not return the first two characters. What gave you that idea?

    · Share on TwitterShare on Facebook
Sign In or Register to comment.