Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • World
  • Users
  • Groups
Skins
  • Light
  • Cerulean
  • Cosmo
  • Flatly
  • Journal
  • Litera
  • Lumen
  • Lux
  • Materia
  • Minty
  • Morph
  • Pulse
  • Sandstone
  • Simplex
  • Sketchy
  • Spacelab
  • United
  • Yeti
  • Zephyr
  • Dark
  • Cyborg
  • Darkly
  • Quartz
  • Slate
  • Solar
  • Superhero
  • Vapor

  • Default (No Skin)
  • No Skin
Collapse
Code Project
  1. Home
  2. Database & SysAdmin
  3. Database
  4. Solved:Update Multiple Rows of one table on the base of multiple rows in another table

Solved:Update Multiple Rows of one table on the base of multiple rows in another table

Scheduled Pinned Locked Moved Database
csharpdatabasecomhelp
15 Posts 4 Posters 0 Views 1 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • V Varsha Ramnani

    you can try the following syntax

    Update LatestPrice set price = LatestPricesFromHistory.price,...other columns From
    (
    here goes query to find highest date data from History table
    ) as LatestPricesFromHistory
    where LatestPrice.SID = LatestPricesFromHistory.SID and LatestPrice.Country = LatestPricesFromHistory.Country

    "Insanity is doing the same thing over and over again but expecting different results.” — Rita Mae Brown

    R Offline
    R Offline
    Rupesh Kumar Swami
    wrote on last edited by
    #3

    Hi Varsha, i tried as following

    UPDATE LatestPrice SET price = LatestPricesFromHistory.price,
    FROM ( SELECT *
    FROM history
    WHERE Date1 = (
    SELECT max( Date1 )
    FROM history ) ) AS LatestPricesFromHistory
    WHERE LatestPrice.Sid = LatestPricesFromHistory.SId and LatestPrice.Country = LatestPricesFromHistory.Country

    but it give me following error #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'From { select * from history where Date1=(select max(Date1) from history' at line 1. Please suggest

    rup28aug@yahoo.co.in Rupesh Kumar Swami Software Developer, Integrated Solution, Bikaner (India) Company - ISOL, India Award: Best VB.NET article of June 2008: Create Column Charts Using OWC11

    V 1 Reply Last reply
    0
    • R Rupesh Kumar Swami

      Hi All, I want to update multiple columns of multiple rows of one table. Single/Multiple column update for single/Multiple row is quite easy but i want to update the rows of one table on the basis of rows of other table. Here I demonstrate that what i want with single query Table1 - History Fields and Data Date SId Country Price High Low ........ 1 Dec 1 US 2 3 1 values... 1 Dec 2 US 3 4 2 values... 1 Dec 3 US 4 5 3 values... 1 Dec 4 US 5 6 4 values... 2 Dec 1 US 7 8 5 values... 2 Dec 2 US 8 9 6 values... 2 Dec 3 US 9 10 7 values... 2 Dec 4 US 10 11 8 values... Table2 _ LatestPrice Fields and Data SId Country Price High Low ........ 1 US 1 2 1 values... 2 US 1 2 1 values... 3 US 1 2 1 values... 4 US 1 2 1 values... I want to update the LatestPrice table data on the base of field SID and Country from History table data.Here i pick the only highest date data from History table. Now i want to update data in LatestPrice table. so there are two approaches One way is the pick one by one record from resultset and update the LatestPrice on the base of field SID and Country. so there are mulitple queries depends on the no. of records in resultset Other way is write singe query in this way that they find automatically SId and Country and update the data. So i was unable to write single query for update the Data. Please suggest that how can i do this taks any help will be appreciated

      rup28aug@yahoo.co.in Rupesh Kumar Swami Software Developer, Integrated Solution, Bikaner (India) Company - ISOL, India Award: Best VB.NET article of June 2008: Create Column Charts Using OWC11

      J Offline
      J Offline
      Jorgen Andersson
      wrote on last edited by
      #4

      I'll probably take some heat for suggesting a Merge, but as I assume there might be new SIds and countries appearing in the history table...

      MERGE INTO LatestPrice lp
      USING (
      SELECT Max(DATE),SId,Country,Price,High,Low
      FROM history h
      GROUP BY SId,Country,Price,High,Low
      ON lp.SId = h.SId AND lp.Country = h.Country
      WHEN MATCHED THEN UPDATE
      SET lp.Price = h.Price
      ,lp.High = h.High
      ,lp.Low = h.Low
      WHEN NOT MATCHED THEN
      INSERT (lp.sid,lp.Country,lp.Price,lp.High,lp.Low)
      VALUES (h.SId,h.Country,h.Price,h.High,h.Low)

      Another approach is ofcourse to not have a separate table for the LatestPrice, but a view. Which of course might have performance issues.

      CREATE OR REPLACE VIEW LatestPrice (lastdate,SId,Country,Price,High,Low)
      AS (
      SELECT Max(DATE) lastdate,SId,Country,Price,High,Low
      FROM history h
      GROUP BY SId,Country,Price,High,Low
      )

      Edit: changed the view.

      Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions

      R 2 Replies Last reply
      0
      • R Rupesh Kumar Swami

        Hi Varsha, i tried as following

        UPDATE LatestPrice SET price = LatestPricesFromHistory.price,
        FROM ( SELECT *
        FROM history
        WHERE Date1 = (
        SELECT max( Date1 )
        FROM history ) ) AS LatestPricesFromHistory
        WHERE LatestPrice.Sid = LatestPricesFromHistory.SId and LatestPrice.Country = LatestPricesFromHistory.Country

        but it give me following error #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'From { select * from history where Date1=(select max(Date1) from history' at line 1. Please suggest

        rup28aug@yahoo.co.in Rupesh Kumar Swami Software Developer, Integrated Solution, Bikaner (India) Company - ISOL, India Award: Best VB.NET article of June 2008: Create Column Charts Using OWC11

        V Offline
        V Offline
        Varsha Ramnani
        wrote on last edited by
        #5

        I think MySQL does not support Update-Select. I have used this syntax in SQL server 2005. try this one for MySQL :

        Update LatestPrice,
        ( Your Query for finding Latest Prices goes here) as LatestPriceFromHistory
        Set price = LatestPriceFromHistory.price
        where LatestPrice.SID=LatestPriceFromHistory.SID and LatestPrice.Country = LatestPriceFromHistory.Country

        "Insanity is doing the same thing over and over again but expecting different results.” — Rita Mae Brown

        R 1 Reply Last reply
        0
        • V Varsha Ramnani

          I think MySQL does not support Update-Select. I have used this syntax in SQL server 2005. try this one for MySQL :

          Update LatestPrice,
          ( Your Query for finding Latest Prices goes here) as LatestPriceFromHistory
          Set price = LatestPriceFromHistory.price
          where LatestPrice.SID=LatestPriceFromHistory.SID and LatestPrice.Country = LatestPriceFromHistory.Country

          "Insanity is doing the same thing over and over again but expecting different results.” — Rita Mae Brown

          R Offline
          R Offline
          Rupesh Kumar Swami
          wrote on last edited by
          #6

          Hi Varsha, i update the query as you suggested. and following is the original query UPDATE issdetails_new a, ( SELECT secid, country, PriceDate, OPEN , High, Low, Close, Ask, midval, Last, Bid, BidSize, AskSize, TradedVolume, MktCloseDate, Volflag, TradedValue, TotalTrades, COMMENT , LocalCode FROM hist_prices WHERE MktCloseDate = ( SELECT max( MktCloseDate ) FROM hist_prices ) ) AS b SET a.PriceDate = b.PriceDate, a.Open = b.Open, a.High = b.High, a.Low = b.Low, a.Close = b.Close, a.Midval = b.Midval, a.Ask = b.Ask, a.Last = b.Last, a.Bid = b.Bid, a.BidSize = b.BidSize, a.AskSize = b.AskSize, a.TradedVolume = b.TradedVolume, a.MktCloseDate = b.MktCloseDate, a.Volflag = b.Volflag, a.TradedValue = b.TradedValue, a.TotalTrades = b.TotalTrades, a.Comment = b.Comment, a.LocalCode = b.LocalCode WHERE a.SecId = b.SecId AND a.Country = b.Country Its works well. Thanks a lot but there are one more problem in table issdetails, there are 9500 records and in table hist_prices there are 41,50,000 records so it takes 11 -13 minute for update the table issdetails. Any option to reduce the execution time ? Please suggest

          rup28aug@yahoo.co.in Rupesh Kumar Swami Software Developer, Integrated Solution, Bikaner (India) Company - ISOL, India Award: Best VB.NET article of June 2008: Create Column Charts Using OWC11

          V 1 Reply Last reply
          0
          • J Jorgen Andersson

            I'll probably take some heat for suggesting a Merge, but as I assume there might be new SIds and countries appearing in the history table...

            MERGE INTO LatestPrice lp
            USING (
            SELECT Max(DATE),SId,Country,Price,High,Low
            FROM history h
            GROUP BY SId,Country,Price,High,Low
            ON lp.SId = h.SId AND lp.Country = h.Country
            WHEN MATCHED THEN UPDATE
            SET lp.Price = h.Price
            ,lp.High = h.High
            ,lp.Low = h.Low
            WHEN NOT MATCHED THEN
            INSERT (lp.sid,lp.Country,lp.Price,lp.High,lp.Low)
            VALUES (h.SId,h.Country,h.Price,h.High,h.Low)

            Another approach is ofcourse to not have a separate table for the LatestPrice, but a view. Which of course might have performance issues.

            CREATE OR REPLACE VIEW LatestPrice (lastdate,SId,Country,Price,High,Low)
            AS (
            SELECT Max(DATE) lastdate,SId,Country,Price,High,Low
            FROM history h
            GROUP BY SId,Country,Price,High,Low
            )

            Edit: changed the view.

            Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions

            R Offline
            R Offline
            Rupesh Kumar Swami
            wrote on last edited by
            #7

            HI Jorgen thanks a lot for you suggestion. View option is not applicable for my requirement. since I need to update the data in table. Also i tried the Merge option as you suggested but with a little modification and it is the original query MERGE INTO issdetails_new lp USING ( SELECT Max( MktCloseDate ) , secid,country, PriceDate, OPEN , High, Low, Close, Ask, Last, Bid, BidSize, AskSize, TradedVolume, MktCloseDate, Volflag, TradedValue, TotalTrades, COMMENT , LocalCode FROM hist_prices GROUP BY secid, PriceDate, OPEN , High, Low, Close, Ask, Last, Bid, BidSize, AskSize, TradedVolume, MktCloseDate, Volflag, TradedValue, TotalTrades, COMMENT , LocalCode ) AS h ON (lp.Secid = h.Secid and lp.country=h.country) WHEN MATCHED THEN UPDATE SET lp.PriceDate = h.PriceDate, lp.open = h.open, lp.Low = h.Low, lp.high = h.high, lp.close = h.close, lp.ask = h.ask, lp.last = h.last, lp.bid = h.bid, lp.bidsize = h.bidsize, lp.asksize = h.asksize, lp.TradedVolume = h.TradedVolume, lp.MktCloseDate = h.MktCloseDate, lp.Volflag = h.Volflag, lp.TradedValue = h.TradedValue, lp.TotalTrades = h.TotalTrades, lp.Comment = h.Comment, lp.LocalCode = h.LocalCode but it gives me following error #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MERGE INTO issdetails_new lp USING ( SELECT Max(MktCloseDate),secid,Pric' at line 1 Please suggest where i am doing wrong.

            rup28aug@yahoo.co.in Rupesh Kumar Swami Software Developer, Integrated Solution, Bikaner (India) Company - ISOL, India Award: Best VB.NET article of June 2008: Create Column Charts Using OWC11

            J 1 Reply Last reply
            0
            • R Rupesh Kumar Swami

              HI Jorgen thanks a lot for you suggestion. View option is not applicable for my requirement. since I need to update the data in table. Also i tried the Merge option as you suggested but with a little modification and it is the original query MERGE INTO issdetails_new lp USING ( SELECT Max( MktCloseDate ) , secid,country, PriceDate, OPEN , High, Low, Close, Ask, Last, Bid, BidSize, AskSize, TradedVolume, MktCloseDate, Volflag, TradedValue, TotalTrades, COMMENT , LocalCode FROM hist_prices GROUP BY secid, PriceDate, OPEN , High, Low, Close, Ask, Last, Bid, BidSize, AskSize, TradedVolume, MktCloseDate, Volflag, TradedValue, TotalTrades, COMMENT , LocalCode ) AS h ON (lp.Secid = h.Secid and lp.country=h.country) WHEN MATCHED THEN UPDATE SET lp.PriceDate = h.PriceDate, lp.open = h.open, lp.Low = h.Low, lp.high = h.high, lp.close = h.close, lp.ask = h.ask, lp.last = h.last, lp.bid = h.bid, lp.bidsize = h.bidsize, lp.asksize = h.asksize, lp.TradedVolume = h.TradedVolume, lp.MktCloseDate = h.MktCloseDate, lp.Volflag = h.Volflag, lp.TradedValue = h.TradedValue, lp.TotalTrades = h.TotalTrades, lp.Comment = h.Comment, lp.LocalCode = h.LocalCode but it gives me following error #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MERGE INTO issdetails_new lp USING ( SELECT Max(MktCloseDate),secid,Pric' at line 1 Please suggest where i am doing wrong.

              rup28aug@yahoo.co.in Rupesh Kumar Swami Software Developer, Integrated Solution, Bikaner (India) Company - ISOL, India Award: Best VB.NET article of June 2008: Create Column Charts Using OWC11

              J Offline
              J Offline
              Jorgen Andersson
              wrote on last edited by
              #8

              That's not a very descriptive error message. And my experience with mysql is none. But I can see one error in the USING query, you have "Country" in the SELECT but not in the GROUP BY.

              Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions

              R 1 Reply Last reply
              0
              • J Jorgen Andersson

                That's not a very descriptive error message. And my experience with mysql is none. But I can see one error in the USING query, you have "Country" in the SELECT but not in the GROUP BY.

                Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions

                R Offline
                R Offline
                Rupesh Kumar Swami
                wrote on last edited by
                #9

                HI Jorgen, I think this is not the cause for error. since it only not include the country in Grouping.

                rup28aug@yahoo.co.in Rupesh Kumar Swami Software Developer, Integrated Solution, Bikaner (India) Company - ISOL, India Award: Best VB.NET article of June 2008: Create Column Charts Using OWC11

                J 1 Reply Last reply
                0
                • R Rupesh Kumar Swami

                  Hi Varsha, i update the query as you suggested. and following is the original query UPDATE issdetails_new a, ( SELECT secid, country, PriceDate, OPEN , High, Low, Close, Ask, midval, Last, Bid, BidSize, AskSize, TradedVolume, MktCloseDate, Volflag, TradedValue, TotalTrades, COMMENT , LocalCode FROM hist_prices WHERE MktCloseDate = ( SELECT max( MktCloseDate ) FROM hist_prices ) ) AS b SET a.PriceDate = b.PriceDate, a.Open = b.Open, a.High = b.High, a.Low = b.Low, a.Close = b.Close, a.Midval = b.Midval, a.Ask = b.Ask, a.Last = b.Last, a.Bid = b.Bid, a.BidSize = b.BidSize, a.AskSize = b.AskSize, a.TradedVolume = b.TradedVolume, a.MktCloseDate = b.MktCloseDate, a.Volflag = b.Volflag, a.TradedValue = b.TradedValue, a.TotalTrades = b.TotalTrades, a.Comment = b.Comment, a.LocalCode = b.LocalCode WHERE a.SecId = b.SecId AND a.Country = b.Country Its works well. Thanks a lot but there are one more problem in table issdetails, there are 9500 records and in table hist_prices there are 41,50,000 records so it takes 11 -13 minute for update the table issdetails. Any option to reduce the execution time ? Please suggest

                  rup28aug@yahoo.co.in Rupesh Kumar Swami Software Developer, Integrated Solution, Bikaner (India) Company - ISOL, India Award: Best VB.NET article of June 2008: Create Column Charts Using OWC11

                  V Offline
                  V Offline
                  Varsha Ramnani
                  wrote on last edited by
                  #10

                  Sorry I don't know much about MySQL, but you can google for "Indexing in MySQL".

                  "Insanity is doing the same thing over and over again but expecting different results.” — Rita Mae Brown

                  R 1 Reply Last reply
                  0
                  • V Varsha Ramnani

                    Sorry I don't know much about MySQL, but you can google for "Indexing in MySQL".

                    "Insanity is doing the same thing over and over again but expecting different results.” — Rita Mae Brown

                    R Offline
                    R Offline
                    Rupesh Kumar Swami
                    wrote on last edited by
                    #11

                    Thanks a ton Varsha Your suggestion works well Now It takes only 8 - 9 seconds

                    rup28aug@yahoo.co.in Rupesh Kumar Swami Software Developer, Integrated Solution, Bikaner (India) Company - ISOL, India Award: Best VB.NET article of June 2008: Create Column Charts Using OWC11

                    1 Reply Last reply
                    0
                    • J Jorgen Andersson

                      I'll probably take some heat for suggesting a Merge, but as I assume there might be new SIds and countries appearing in the history table...

                      MERGE INTO LatestPrice lp
                      USING (
                      SELECT Max(DATE),SId,Country,Price,High,Low
                      FROM history h
                      GROUP BY SId,Country,Price,High,Low
                      ON lp.SId = h.SId AND lp.Country = h.Country
                      WHEN MATCHED THEN UPDATE
                      SET lp.Price = h.Price
                      ,lp.High = h.High
                      ,lp.Low = h.Low
                      WHEN NOT MATCHED THEN
                      INSERT (lp.sid,lp.Country,lp.Price,lp.High,lp.Low)
                      VALUES (h.SId,h.Country,h.Price,h.High,h.Low)

                      Another approach is ofcourse to not have a separate table for the LatestPrice, but a view. Which of course might have performance issues.

                      CREATE OR REPLACE VIEW LatestPrice (lastdate,SId,Country,Price,High,Low)
                      AS (
                      SELECT Max(DATE) lastdate,SId,Country,Price,High,Low
                      FROM history h
                      GROUP BY SId,Country,Price,High,Low
                      )

                      Edit: changed the view.

                      Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions

                      R Offline
                      R Offline
                      Rupesh Kumar Swami
                      wrote on last edited by
                      #12

                      HI Jorgen, My problem is solved. please check this thread. Thanks for suggest new way of update and Insert. I was not aware of this way.

                      rup28aug@yahoo.co.in Rupesh Kumar Swami Software Developer, Integrated Solution, Bikaner (India) Company - ISOL, India Award: Best VB.NET article of June 2008: Create Column Charts Using OWC11

                      1 Reply Last reply
                      0
                      • R Rupesh Kumar Swami

                        HI Jorgen, I think this is not the cause for error. since it only not include the country in Grouping.

                        rup28aug@yahoo.co.in Rupesh Kumar Swami Software Developer, Integrated Solution, Bikaner (India) Company - ISOL, India Award: Best VB.NET article of June 2008: Create Column Charts Using OWC11

                        J Offline
                        J Offline
                        Jorgen Andersson
                        wrote on last edited by
                        #13

                        You're right, it seems that the problem is that MySQL isn't supporting the Merge command. But no problem, they have one of their own instead[^].

                        Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions

                        1 Reply Last reply
                        0
                        • V Varsha Ramnani

                          you can try the following syntax

                          Update LatestPrice set price = LatestPricesFromHistory.price,...other columns From
                          (
                          here goes query to find highest date data from History table
                          ) as LatestPricesFromHistory
                          where LatestPrice.SID = LatestPricesFromHistory.SID and LatestPrice.Country = LatestPricesFromHistory.Country

                          "Insanity is doing the same thing over and over again but expecting different results.” — Rita Mae Brown

                          L Offline
                          L Offline
                          Lost User
                          wrote on last edited by
                          #14

                          The typeface of the font used in your name is annoying.

                          Bastard Programmer from Hell :suss:

                          V 1 Reply Last reply
                          0
                          • L Lost User

                            The typeface of the font used in your name is annoying.

                            Bastard Programmer from Hell :suss:

                            V Offline
                            V Offline
                            Varsha Ramnani
                            wrote on last edited by
                            #15

                            Sorry... I changed it :) Thanks for the suggestion.

                            "Insanity is doing the same thing over and over again but expecting different results.” — Rita Mae Brown

                            1 Reply Last reply
                            0
                            Reply
                            • Reply as topic
                            Log in to reply
                            • Oldest to Newest
                            • Newest to Oldest
                            • Most Votes


                            • Login

                            • Don't have an account? Register

                            • Login or register to search.
                            • First post
                              Last post
                            0
                            • Categories
                            • Recent
                            • Tags
                            • Popular
                            • World
                            • Users
                            • Groups