UPDATE JOIN TOP 1

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Darren Woodbrey
    #1

    UPDATE JOIN TOP 1

    I am trying to update 1 table with the top records from another table for
    each record in the first table

    UPDATE HPFSLOWMOVING
    SET TOP 1 LASTRCTDATE = (SELECT DOCDATE FROM IV30300 INNER JOIN
    HPFSLOWMOVING ON HPFSLOWMOVING.I TEMNMBR = IV30300.ITEMNMB R AND
    HPFSLOWMOVING.L OCNCODE = IV30300.LOCNCOD E WHERE DOCTYPE = 4)

    This updates all records with the same lastrctdate. I need to update each
    records with the top lastrctdate where the itemnmbr and locncode equals.
    Thanks for any help you can provide!


    Darren


    Tags: None
    • Darren Woodbrey
      #2
      Re: UPDATE JOIN TOP 1

      My query below is wrong. This is what I have so far:

      UPDATE HPFSLOWMOVING
      SET LASTRCTDATE = (SELECT TOP 1 DOCDATE FROM IV30300 INNER JOIN
      HPFSLOWMOVING ON HPFSLOWMOVING.I TEMNMBR = IV30300.ITEMNMB R AND
      HPFSLOWMOVING.L OCNCODE = IV30300.LOCNCOD E WHERE DOCTYPE = 4)



      "Darren Woodbrey" <it@hpfairfield .comwrote in message
      news:12monclfgt 9923a@corp.supe rnews.com...
      >I am trying to update 1 table with the top records from another table for
      >each record in the first table
      >
      UPDATE HPFSLOWMOVING
      SET TOP 1 LASTRCTDATE = (SELECT DOCDATE FROM IV30300 INNER JOIN
      HPFSLOWMOVING ON HPFSLOWMOVING.I TEMNMBR = IV30300.ITEMNMB R AND
      HPFSLOWMOVING.L OCNCODE = IV30300.LOCNCOD E WHERE DOCTYPE = 4)
      >
      This updates all records with the same lastrctdate. I need to update each
      records with the top lastrctdate where the itemnmbr and locncode equals.
      Thanks for any help you can provide!
      >
      >
      Darren
      >

        Comment

        • Roy Harvey
          #3
          Re: UPDATE JOIN TOP 1

          I see two problems. One is that the subquery uses TOP without an
          ORDER BY. Without ORDER BY, TOP simply returns any one row.

          The other is that the subquery's FROM clause should NOT include the
          table being updated.

          Try something along the lines of this:

          UPDATE HPFSLOWMOVING
          SET LASTRCTDATE =
          (SELECT TOP 1 DOCDATE
          FROM IV30300
          WHERE HPFSLOWMOVING.I TEMNMBR = IV30300.ITEMNMB R
          AND HPFSLOWMOVING.L OCNCODE = IV30300.LOCNCOD E
          AND DOCTYPE = 4
          ORDER BY ??????)

          Roy Harvey
          Beacon Falls, CT

          On Tue, 28 Nov 2006 11:13:55 -0500, "Darren Woodbrey"
          <it@hpfairfield .comwrote:
          >My query below is wrong. This is what I have so far:
          >
          >UPDATE HPFSLOWMOVING
          >SET LASTRCTDATE = (SELECT TOP 1 DOCDATE FROM IV30300 INNER JOIN
          >HPFSLOWMOVIN G ON HPFSLOWMOVING.I TEMNMBR = IV30300.ITEMNMB R AND
          >HPFSLOWMOVING. LOCNCODE = IV30300.LOCNCOD E WHERE DOCTYPE = 4)
          >
          >
          >
          >"Darren Woodbrey" <it@hpfairfield .comwrote in message
          >news:12monclfg t9923a@corp.sup ernews.com...
          >>I am trying to update 1 table with the top records from another table for
          >>each record in the first table
          >>
          >UPDATE HPFSLOWMOVING
          >SET TOP 1 LASTRCTDATE = (SELECT DOCDATE FROM IV30300 INNER JOIN
          >HPFSLOWMOVIN G ON HPFSLOWMOVING.I TEMNMBR = IV30300.ITEMNMB R AND
          >HPFSLOWMOVING. LOCNCODE = IV30300.LOCNCOD E WHERE DOCTYPE = 4)
          >>
          >This updates all records with the same lastrctdate. I need to update each
          >records with the top lastrctdate where the itemnmbr and locncode equals.
          >Thanks for any help you can provide!
          >>
          >>
          >Darren

            Comment

            • Darren Woodbrey
              #4
              Re: UPDATE JOIN TOP 1

              That is it. Thanks so much for your help!


              "Roy Harvey" <roy_harvey@sne t.netwrote in message
              news:uvoom2tk8p uai2qv061fr23fk hdqargcg9@4ax.c om...
              >I see two problems. One is that the subquery uses TOP without an
              ORDER BY. Without ORDER BY, TOP simply returns any one row.
              >
              The other is that the subquery's FROM clause should NOT include the
              table being updated.
              >
              Try something along the lines of this:
              >
              UPDATE HPFSLOWMOVING
              SET LASTRCTDATE =
              (SELECT TOP 1 DOCDATE
              FROM IV30300
              WHERE HPFSLOWMOVING.I TEMNMBR = IV30300.ITEMNMB R
              AND HPFSLOWMOVING.L OCNCODE = IV30300.LOCNCOD E
              AND DOCTYPE = 4
              ORDER BY ??????)
              >
              Roy Harvey
              Beacon Falls, CT
              >
              On Tue, 28 Nov 2006 11:13:55 -0500, "Darren Woodbrey"
              <it@hpfairfield .comwrote:
              >
              >>My query below is wrong. This is what I have so far:
              >>
              >>UPDATE HPFSLOWMOVING
              >>SET LASTRCTDATE = (SELECT TOP 1 DOCDATE FROM IV30300 INNER JOIN
              >>HPFSLOWMOVI NG ON HPFSLOWMOVING.I TEMNMBR = IV30300.ITEMNMB R AND
              >>HPFSLOWMOVING .LOCNCODE = IV30300.LOCNCOD E WHERE DOCTYPE = 4)
              >>
              >>
              >>
              >>"Darren Woodbrey" <it@hpfairfield .comwrote in message
              >>news:12monclf gt9923a@corp.su pernews.com...
              >>>I am trying to update 1 table with the top records from another table for
              >>>each record in the first table
              >>>
              >>UPDATE HPFSLOWMOVING
              >>SET TOP 1 LASTRCTDATE = (SELECT DOCDATE FROM IV30300 INNER JOIN
              >>HPFSLOWMOVI NG ON HPFSLOWMOVING.I TEMNMBR = IV30300.ITEMNMB R AND
              >>HPFSLOWMOVING .LOCNCODE = IV30300.LOCNCOD E WHERE DOCTYPE = 4)
              >>>
              >>This updates all records with the same lastrctdate. I need to update
              >>each
              >>records with the top lastrctdate where the itemnmbr and locncode equals.
              >>Thanks for any help you can provide!
              >>>
              >>>
              >>Darren

                Comment

                • Erland Sommarskog
                  #5
                  Re: UPDATE JOIN TOP 1

                  Roy Harvey (roy_harvey@sne t.net) writes:
                  The other is that the subquery's FROM clause should NOT include the
                  table being updated.
                  And then people keep yelling on the FROM syntax of being dangerous!
                  --
                  Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

                  Books Online for SQL Server 2005 at
                  http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

                  Books Online for SQL Server 2000 at
                  http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

                    Comment

                    Previous template Next
                    Working...