Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2014
    Posts
    4

    Unanswered: DB2 multiple records update in single column

    HI,

    I need some assistance in writing an update sql query. I am having hard time finding this on google or the search feature on this forum. Here is what I would like to update.

    I have a table (TABLE_1) with n rows.
    I want to update a single column (EMAIL_ID) from "XX@abcd.com" to "XX@xyz.com" for the rows which has "XX@abcd.com"

    Any direction would be greatly appreciated.

    Thanks,
    Vijay

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    if this is what you are asking :
    update tabname set col='XX@xyz.com' where col='XX@abcd.com'

    this, my grandson of 2 would even know..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Jul 2014
    Posts
    4
    Hi,

    I need it n number of rows not for the single row.
    And I doesn't know how many rows satisfies the criteria.

    Thanks
    Vijay

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by vijayabaskar View Post
    I need it n number of rows not for the single row.
    And I doesn't know how many rows satisfies the criteria.
    So someone gives you the answer for N number of rows. What will you do if you then need M rows?

    SQLCourse - Lesson 6: Updating Records
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Jul 2014
    Posts
    4
    Hi,


    I found out the query. Please find the same

    UPDATE dbowner name.table name SET EMAIL_ADDR =
    CONCAT(
    SUBSTR(EMAIL_ADDR,1,(LOCATE('@',DS_EMAIL_ADDRESS)) ),'XYZ.COM')
    WHERE EMAIL_ADDR LIKE '%@ABC.COM';

    The query output is EMAIL_ADDR = @XYZ.COM for all the rows which contains EMAIL_ADDR = @ABC.COM(EMAIL_ADDR - Variable length column)

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Vijay,
    Are you stating the SQL that Marcus gave you only updated one row and not all that met the where clause filtration?
    Dave

  7. #7
    Join Date
    Aug 2014
    Posts
    4
    I would add "%" in WHERE EMAIL_ADDR LIKE '%@ABC.COM';
    after COM.

    Do a COUNT, before update, using same WHERE

    gives you a better Idea on what you about to update.

  8. #8
    Join Date
    Aug 2014
    Posts
    4
    is it possible that you are dealing with mix of Upper and lower case letters?

    Convert everything to UPPER case in your WHERE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •