Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2015
    Posts
    27

    Post Unanswered: update statement inclusion

    have this update statement that works for one record. How do I write it to include multiple records at once. Please see sample below.



    update

    mklopt

    set


    FRMDAT = '12/31/2014'


    where


    JOBCOD = 'PH14789'


    I also want to include the following instead of running it one at a time

    PH17523

    PH17524

    PH17525

    PH17553

    PH17555

    PH17556

    PH17557

    PH17558

    PH17571

    PH17573

    PH17574

    PH17575

    PH17576

    PH17577

    PH1757

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Do you have a SELECT statement that will generate your list of JOBCOD values, or do those come from an application?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Do you mean like an IN list???

    [where JOBCOD in ('PH14789',....'PH1757' )[/CODE]
    Dave

  4. #4
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Code:
    update mklopt
    
    set frmdat = '12/31/2014'
    
    where jobcod in (
    
    ph17523 , 
    
    ph17524 ,
    
    ph17525 ,
    
    ph17553 ,
    
    ph17555 ,
    
    ph17556 ,
    
    ph17557 ,
    
    ph17558 ,
    
    ph17571 ,
    
    ph17573 ,
    
    ph17574 ,
    
    ph17575 ,
    
    ph17576 ,
    
    ph17577 ,
    
    ph17579 )
    I think this is what you want. Run it as a select first to see if you get your intended results.
    Last edited by VLOOKUP; 04-21-15 at 16:01.

  5. #5
    Join Date
    Jan 2015
    Posts
    27
    VLOOKUP is correct. What if the requirement changed to set the values in the list that are greater than 12/31/2014 to 12/31/2014...

    I created this unique codes and


    I need all [FRMDAT] field set to ‘12/31/2014’ in the MKLOPT table, where the [JOBCOD] is in the VALUE list BELOW and the [FRMDAT] is currently > ‘12/31/2014’




    VALUE LIST


    PH00059


    PH02775


    PH03051


    PH03305


    PH03336


    PH03342


    PH03371

  6. #6
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Code:
    update mklopt
    
    set frmdat = '12/31/2014'
    
    where frmdat > '12/31/2014' and jobcod in (
    
    ph17523 , 
    
    ph17524 ,
    
    ph17525 ,
    
    ph17553 ,
    
    ph17555 ,
    
    ph17556 ,
    
    ph17557 ,
    
    ph17558 ,
    
    ph17571 ,
    
    ph17573 ,
    
    ph17574 ,
    
    ph17575 ,
    
    ph17576 ,
    
    ph17577 ,
    
    ph17579 ))
    If the date range is a string you may need to cast or convert it. Make sure to run it as a select first.

    HTH
    Last edited by VLOOKUP; 04-21-15 at 18:10.

Tags for this Thread

Posting Permissions

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