Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New to the CF scene
    Join Date
    Feb 2012
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    insert/update data in a table from other table data

    I have two tables. One is tbshiftrpt and other is tbdayrpt.

    columns in table tbshiftrpt are id, date,shift,machineName,WH,IH,BH,MH,operator,remark

    columns in table tbdayrpt are id, date,machineName,WH,IH,BH,MH,operator,remark

    table tbshiftrpt has data such as .
    1, 12/02/12 ,A ,fel-07,4,2,1,1, xxx xxx, xxxxxxxxxxxx
    2, 12/02/12,B,fel-07,1,1,1,5,xxxxxx xx,xxxxxxxxxxxxx
    3, 12/02/12,B,fel-08,1,1,2,4,xxxxxx xx,xxxxxxxxxxxxx
    4, 12/02/12,c,fel-08,1,1,1,5,xxxxxx xx,xxxxxxxxxxxxx

    day is divided in three shift a b c
    we have 06 machine such as fel-06,fel-07,fel-08,fel-09

    how to write a script to enter data in tbdayrpt from tbshiftrpt
    show that wh,mh,ih,bh is added up for a mchine for a day i.e shift a shift b shift c

    1,12/02/12,fel-07,5,3,1,6, xxx xxx & xxxxxx xx, xxxxxxxxxxxx & xxxxxxxxxxxxx

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,437
    Thanks
    75
    Thanked 4,372 Times in 4,337 Posts
    Easy.

    But there's no reason to even have the tbdayrpt as a table! Just make it a VIEW on tbshiftrpt
    Code:
    CREATE VIEW tbdayrpt
    AS
    SELECT date, machineName,
           SUM(WH) AS WH, SUM(IH) AS IH, SUM(BH) AS BH, SUM(MH) AS MH,
           GROUP_CONCAT(operator SEPARATOR '; ') AS operator,
           GROUP_CONCAT(remark SEPARATOR '; ' ) AS remark
    FROM tbshiftrpt
    GROUP BY date, machineName
    ORDER BY date, machineName
    If you really *MUST* insert the data into the other table, then:
    Code:
    INSERT INTO tbdayrpt ( date,machineName,WH,IH,BH,MH,operator,remark )
    SELECT date, machineName,
           SUM(WH) AS WH, SUM(IH) AS IH, SUM(BH) AS BH, SUM(MH) AS MH,
           GROUP_CONCAT(operator SEPARATOR '; ') AS operator,
           GROUP_CONCAT(remark SEPARATOR '; ' ) AS remark
    FROM tbshiftrpt
    GROUP BY date, machineName
    ORDER BY date, machineName
    (You can use whatever you want for the SEPARATOR in those two GROUP_CONCATs, of course.)
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    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
    •