Results 1 to 17 of 17

Thread: [RESOLVED] Update table with CTE;

  1. #1

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,260

    Resolved [RESOLVED] Update table with CTE;

    Hi,
    I want to update a table but I have some questions:
    1)Is this seems correct for an update
    2)It will not update if I put begin tran , commit tran it will start not recognizing columns.

    Code:
    -- not working -- Begin tran
    
     WITH PAY_TICK AS (select TransT_lgnNumber,sum(t.TransT_curValueEach) as total_T from tblTrans_Ticket T where  TransT_strStatus = 'V' and TransT_strType = 'P' 
     AND TransT_dtmDateTime between '20221121 06:00:00' and '20221122 06:00:00' and Workstation_strCode = 'salcons2'  group by TransT_lgnNumber)
     ,PAY_INV as (Select TransI_lgnNumber,sum(TransI_curValueEach * TransI_decNoOfItems) total_I from tblTrans_Inventory  
     where TransI_dtmDateTime between '20221121 06:00:00' and '20221122 06:00:00' and Workstation_strCode = 'salcons2' group by TransI_lgnNumber  ),
     PAY_TOTAL as (Select  TransT_lgnNumber,total_T FROM PAY_TICK UNION select TransI_lgnNumber,total_I FROM PAY_INV),
     PAY_TOTAL_SUM as( select TransT_lgnNumber,sum(cast(total_T as money)) as Pay_Total from PAY_TOTAL  group by PAY_TOTAL.TransT_lgnNumber)
    
     UPDATE
        Table_A
    SET
        Table_A.TransC_curValue = Table_B.Pay_Total
    FROM
        tblTrans_Cash AS Table_A
        inner join PAY_TOTAL_SUM  AS Table_B
            ON Table_A.TransC_lgnNumber = Table_B.TransT_lgnNumber
    WHERE
        Table_A.TransC_dtmDateTime between '20221121 06:00:00' and '20221122 06:00:00'and Table_A.TransC_strType='1' and Table_B.Pay_Total <> Table_A.TransC_curValue 
     and Table_A.TransC_curValue >= Table_B.Pay_Total 
     and Table_A.Workstation_strCode = 'salcons6' -- safety measure not to update by accident
    
    -- commit tran -- not working
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  2. #2
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    3,496

    Re: Update table with CTE;

    Properly formatted.
    Spot the mistake

    Code:
    WITH PAY_TICK AS 
        (select 
            TransT_lgnNumber, 
            sum(t.TransT_curValueEach) as total_T 
            from tblTrans_Ticket T 
            where  TransT_strStatus = 'V' and TransT_strType = 'P' AND TransT_dtmDateTime between '20221121 06:00:00' and '20221122 06:00:00' and 
            Workstation_strCode = 'salcons2'  
            group by TransT_lgnNumber),
        PAY_INV as 
        (Select 
            TransI_lgnNumber,
            sum(TransI_curValueEach * TransI_decNoOfItems) total_I 
            from tblTrans_Inventory 
            where TransI_dtmDateTime between '20221121 06:00:00' and '20221122 06:00:00' and 
            Workstation_strCode = 'salcons2' 
            group by TransI_lgnNumber),
        PAY_TOTAL as 
        (Select 
            TransT_lgnNumber,
            total_T FROM PAY_TICK 
        UNION 
        select 
            TransI_lgnNumber,
            total_I FROM PAY_INV), 
        PAY_TOTAL_SUM as 
        (select 
            TransT_lgnNumber,
            sum(cast(total_T as money)) as Pay_Total 
            from PAY_TOTAL  
            group by PAY_TOTAL.TransT_lgnNumber)
    
    UPDATE Table_A
    SET Table_A.TransC_curValue = Table_B.Pay_Total
    FROM tblTrans_Cash AS Table_A
    inner join PAY_TOTAL_SUM AS Table_B
    ON Table_A.TransC_lgnNumber = Table_B.TransT_lgnNumber
    
    WHERE
    Table_A.TransC_dtmDateTime between '20221121 06:00:00' and '20221122 06:00:00'and Table_A.TransC_strType='1' and 
    Table_B.Pay_Total <> Table_A.TransC_curValue and 
    Table_A.TransC_curValue >= Table_B.Pay_Total and 
    Table_A.Workstation_strCode = 'salcons6' -- safety measure not to update by accident
    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  3. #3

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,260

    Re: Update table with CTE;

    ?.......
    Where the heck is it?!
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  4. #4
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    3,496

    Re: Update table with CTE;

    Quote Originally Posted by sapator;[URL="tel:5586862"
    5586862[/URL]]?.......
    Where the heck is it?!
    The first line of the last WHERE-clause: there is a blank missing after your timecheck and the AND

    btw: such an UPDATE is „translatable“ into a SELECT.
    Would rather do this first checking if result is as expected
    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  5. #5

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,260

    Re: Update table with CTE;

    But I don't see any different query on the first line of the last where:

    me:
    Table_A.TransC_dtmDateTime between '20221121 06:00:00' and '20221122 06:00:00'and Table_A.TransC_strType='1' and
    you:
    Table_A.TransC_dtmDateTime between '20221121 06:00:00' and '20221122 06:00:00'and Table_A.TransC_strType='1' and

    Am I missing something or I'm just blind?
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  6. #6
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,425

    Re: Update table with CTE;

    There is no difference only different formatting of the query.
    But the problem Zvoni pointed at is this:
    Code:
    Table_A.TransC_dtmDateTime between '20221121 06:00:00' and '20221122 06:00:00'and Table_A.TransC_strType='1' and
    There is a space missing between :00' and the and

  7. #7
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    3,496

    Re: Update table with CTE;

    btw: Is it just me, or is this Timecheck not ISO-compliant?
    i was under the impression that ISO-Format is including dashs
    would have expected
    Code:
    Table_A.TransC_dtmDateTime between '2022-11-21 06:00:00' and '2022-11-22 06:00:00'and Table_A.TransC_strType='1' and
    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  8. #8

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,260

    Re: Update table with CTE;

    I'm not sure what we are talking about.
    Using , and '2022-11-22 06:00:00'and Table_A.TransC_strType='1' , will work.
    Using Tand '2022-11-22 06:00:00' andTable_A.TransC_strType='1' , will NOT work.
    I don't know if there a difference in database formats but also the SQL studio would have complained. So, I don't know what we are talking about.
    Since the query is OK , is there a way to use begin , commit trans in it?

    Thanks.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  9. #9
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    3,496

    Re: Update table with CTE;

    Quote Originally Posted by sapator View Post
    Using , and '2022-11-22 06:00:00'and Table_A.TransC_strType='1' , will work.
    Thanks.
    Since when should this work in any DBMS?

    And to your initial Question: Is the UPDATE working as a SELECT? As in returning desired results?

    Transformed to a SELECT (and cleaned up formatting), and i've commented out your "saftey-measure"
    Code:
    WITH PAY_TICK AS 
        (SELECT 
            TransT_lgnNumber, 
            SUM(t.TransT_curValueEach) AS total_T 
            FROM tblTrans_Ticket T 
            WHERE 
            TransT_strStatus = 'V' AND 
            TransT_strType = 'P' AND 
            TransT_dtmDateTime BETWEEN '20221121 06:00:00' AND '20221122 06:00:00' AND 
            Workstation_strCode = 'salcons2'  
            GROUP BY TransT_lgnNumber),
        PAY_INV AS 
        (SELECT 
            TransI_lgnNumber,
            SUM(TransI_curValueEach * TransI_decNoOfItems) AS total_I 
            FROM tblTrans_Inventory 
            WHERE 
            TransI_dtmDateTime BETWEEN '20221121 06:00:00' AND '20221122 06:00:00' AND 
                Workstation_strCode = 'salcons2' 
            GROUP BY TransI_lgnNumber),
        PAY_TOTAL AS 
        (SELECT 
            TransT_lgnNumber,
            total_T FROM PAY_TICK 
        UNION 
        SELECT 
            TransI_lgnNumber,
            total_I FROM PAY_INV), 
        PAY_TOTAL_SUM AS  
        (SELECT 
            TransT_lgnNumber,
            SUM(cast(total_T as money)) AS Pay_Total 
            FROM PAY_TOTAL  
            GROUP BY TransT_lgnNumber)
    
    SELECT Table_A.TransC_curValue, Table_B.Pay_Total
    FROM tblTrans_Cash AS Table_A
    INNER JOIN PAY_TOTAL_SUM AS Table_B
    ON Table_A.TransC_lgnNumber = Table_B.TransT_lgnNumber
    
    WHERE
        Table_A.TransC_dtmDateTime BETWEEN '20221121 06:00:00' AND '20221122 06:00:00' AND 
        Table_A.TransC_strType='1' AND 
        Table_B.Pay_Total <> Table_A.TransC_curValue AND 
        Table_A.TransC_curValue >= Table_B.Pay_Total /*AND 
        Table_A.Workstation_strCode = 'salcons6' -- safety measure not to update by accident*/
    Last edited by Zvoni; Nov 23rd, 2022 at 07:04 AM.
    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  10. #10

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,260

    Re: Update table with CTE;

    OK I had the select, you needn't do that but thanks. My question was if it will work on begin commit trans.

    As for the "AND" , it is working fine on every database I have ever used. I would assume the dots(not dots the ' , how the heck is called) mark as and EOL so it can continue with the next command but just assuming.
    So this:
    select * from tblTrans_Cash where TransC_dtmDateTime between '20221122 06:00:00'and'20221123 06:00:00'and TransC_lgnNumber = 13111969

    Will work and produce this (if it's viewable):

    Attachment 186280
    or maybe this attachment:
    Name:  Clipboard01.jpg
Views: 31
Size:  17.6 KB
    Last edited by sapator; Nov 23rd, 2022 at 07:25 AM.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  11. #11
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    3,496

    Re: Update table with CTE;

    My question was if it will work on begin commit trans.
    I asked for the whole Statement as a SELECT, because if the SELECT doesn't work, you have an error somewhere else (which i haven't spotted then).
    If the Statement doesn't work, a Transaction will fail in any way.

    If the Statement does work....
    How are you/do you want to start the Transaction?
    please quote your command verbatim here. I might have a suspicion involving semicolons

    EDIT:
    select * from tblTrans_Cash where TransC_dtmDateTime between '20221122 06:00:00'and'20221123 06:00:00'and TransC_lgnNumber = 13111969
    It definitely doesn't work with IBM DB2. I've had enough typos of that sort to know it doesn't work at least with DB2 (would have to test on SQLite).
    Doesn't change the fact, it's ugly, and can evolve to a hideyhole for bugs.

    EDIT2: Oh, and the whole Error-Message verbatim might help a lot
    Last edited by Zvoni; Nov 23rd, 2022 at 11:34 AM.
    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  12. #12

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,260

    Re: Update table with CTE;

    The transaction is to start at the update but it seems like when I use CTE it will error out some lines saying that the are not declared.
    I agree that "AND" looks ugly, I most probably forgot a space when I was writing it as it was requested as "urgent" but for better or for worse, it will work.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  13. #13
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    3,496

    Re: Update table with CTE;

    Quote Originally Posted by sapator;[URL="tel:5586957"
    5586957[/URL]]The transaction is to start at the update but it seems like when I use CTE it will error out some lines saying that the are not declared.
    This is not making any sense.
    are you finishing the command to start the transaction with a semicolon?
    Code:
    begin transaction;
    TheBigUpdateStatement;
    Commit transaction;
    check the semicolons
    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  14. #14

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,260

    Re: Update table with CTE;

    Unbelievable! The semicolons! The @#$@#$ semicolons. I'm not sure when they are required and when not but it seems that CTE update requires semicolons.
    Well, you learn something new every day.
    Thanks Zvoni.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  15. #15
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    3,496

    Re: Update table with CTE;

    Quote Originally Posted by sapator View Post
    Unbelievable! The semicolons! The @#$@#$ semicolons. I'm not sure when they are required and when not but it seems that CTE update requires semicolons.
    Well, you learn something new every day.
    Thanks Zvoni.
    I don't think it has to do with/without CTE
    More with: Those are distinct commands sent to the Database, and the Database has to execute them separately (each command has to be executed for itself).

    No Semicolon --> The DB interprets the whole thing as a single command.

    Think about it: A lot of people start a transaction, then execute some SQL-Statement (Update/Insert/Delete), and then stop execution, because they are asking the User "Is the Result OK?",
    because if Yes --> commit, if No --> Rollback.
    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  16. #16

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,260

    Re: [RESOLVED] Update table with CTE;

    Ye but we have a lot of queries written without semicolons. A recent query I was asking a fix here is 10.000 lines without semicolons (and no CTE). Maybe it's different on other non ms sql setups.
    I would suppose Suppose MS left any standardize queries without ";" for backward compatibility. Probably, maybe, maybe not...
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  17. #17
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    3,496

    Re: [RESOLVED] Update table with CTE;

    Quote Originally Posted by sapator;[URL="tel:5587068"
    5587068[/URL]]Ye but we have a lot of queries written without semicolons. A recent query I was asking a fix here is 10.000 lines without semicolons (and no CTE). Maybe it's different on other non ms sql setups.
    I would suppose Suppose MS left any standardize queries without ";" for backward compatibility. Probably, maybe, maybe not...
    Queries…… Selects, Updates, Inserts, Deletes?
    With/without Transaction?
    I’d analyze it closely before making any assumptions
    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

Posting Permissions

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



Click Here to Expand Forum to Full Width