ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

What is wrong with my SQL statment??

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • What is wrong with my SQL statment??

    Code:
    UPDATE BMARTIN/RCU SET CUCAUS='BMARTIN' FROM BMARTIN/RCU            
    INNER JOIN BMARTIN/RCM ON CUCUST=CCUST WHERE COMP=1 AND CZIP='17847'
    It tells me that keyword FROM not expected. I'm trying to do an update with a join....
    Your future President
    Bryce

    ---------------------------------------------
    http://www.bravobryce.com

  • #2
    Re: What is wrong with my SQL statment??

    I do not beliwve you can join in an update satement unless you are using a subselect.

    Try something like this:

    PHP Code:
    UPDATE BMARTIN/RCU
          SET CUCAUS
    ='BMARTIN'
    Where 
          Exists
    Select 1 From BMARTIN/RCM where CUCUST =  CCUST
          and 
    COMP=1
          
    and CZIP='17847' 
    Predictions are usually difficult, especially about the future. ~Yogi Berra

    Vertical Software Systems
    VSS.biz

    Comment


    • #3
      Re: What is wrong with my SQL statment??

      Hey, what do you know, it worked. I really wish it worked like mySQL instead of this way. Its much easier to understand the other way
      Your future President
      Bryce

      ---------------------------------------------
      http://www.bravobryce.com

      Comment


      • #4
        Re: What is wrong with my SQL statment??

        New problem with UPDATE and JOIN....

        Here is my SQL
        Code:
        update bmartin/slsron set slendt = lodte 
            where exists 
               (select 1 from bpcsffg/ecl where slord = lord and slline = lline)
        But this doesn't work. Of course.

        I need to match on order, and line number and replace one date with another from the other file. Is this possible with SQL? Or do I have to write a little RPG Program to do it?
        Your future President
        Bryce

        ---------------------------------------------
        http://www.bravobryce.com

        Comment


        • #5
          Re: What is wrong with my SQL statment??

          perhaps:
          Code:
          update bmartin/slsron set slendt = (select lodte from bpcsffg/ecl
          where slord=lord and slline = lline)
              where exists 
                 (select 1 from bpcsffg/ecl where slord = lord and slline = lline)
          I'm not anti-social, I just don't like people -Tommy Holden

          Comment


          • #6
            Re: What is wrong with my SQL statment??

            PHP Code:
            update bmartin/slsron set slendt = (select lodte from bpcsffg/ecl
            where slord
            =lord and slline lline
            Predictions are usually difficult, especially about the future. ~Yogi Berra

            Vertical Software Systems
            VSS.biz

            Comment


            • #7
              Re: What is wrong with my SQL statment??

              PHP Code:
              Update bmartin/slsron a set a.slendt = ( Select b.lodte from bpcsffg/ecl b where a.slord b.lord and a.slline b.lline)
                  
              where exists (Select 1 from bpcsffg/ecl b where a.slord b.lord and a.slline b.lline
              Philippe

              Comment


              • #8
                Re: What is wrong with my SQL statment??

                so many choices to choose from... Christmas has come early this year Unless a couple of them are lumps of coal... we'll see won't we
                Your future President
                Bryce

                ---------------------------------------------
                http://www.bravobryce.com

                Comment

                Working...
                X