-
Nov 22nd, 2022, 10:25 AM
#1
[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
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Nov 22nd, 2022, 10:59 AM
#2
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
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
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
-
Nov 22nd, 2022, 12:01 PM
#3
Re: Update table with CTE;
?.......
Where the heck is it?!
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Nov 22nd, 2022, 12:09 PM
#4
Re: Update table with CTE;
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
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
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
-
Nov 23rd, 2022, 02:48 AM
#5
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?
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Nov 23rd, 2022, 04:52 AM
#6
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
-
Nov 23rd, 2022, 05:49 AM
#7
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
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
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
-
Nov 23rd, 2022, 06:34 AM
#8
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.
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Nov 23rd, 2022, 06:48 AM
#9
Re: Update table with CTE;
Originally Posted by sapator
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.
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
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
-
Nov 23rd, 2022, 07:22 AM
#10
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:
Last edited by sapator; Nov 23rd, 2022 at 07:25 AM.
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Nov 23rd, 2022, 11:28 AM
#11
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.
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
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
-
Nov 23rd, 2022, 11:39 AM
#12
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.
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Nov 23rd, 2022, 04:05 PM
#13
Re: Update table with CTE;
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
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
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
-
Nov 24th, 2022, 06:29 AM
#14
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.
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Nov 24th, 2022, 10:06 AM
#15
Re: Update table with CTE;
Originally Posted by sapator
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.
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
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
-
Nov 24th, 2022, 11:50 AM
#16
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...
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Nov 24th, 2022, 12:37 PM
#17
Re: [RESOLVED] Update table with CTE;
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
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|