May 31, 2010 at 5:09 pm
Hello comunity
I have the following accounting movement,
dostamp date account debit credit
XX1 20100101 7112 0 10000
XX1 20100101 2434 0 210
XX1 20100101 211 10210 0
XX1 20100101 612 100 0
XX1 20100101 312 110
I need to make an UPDATE to account 612 to make equal of account 312 value.
for each documents DOSTAMP (Header of my accounting document) is unique for the same document independently the numbers of account i use in my movment accountancy lines , also i build this TSQL statment, and i want to know if it´s correct.
UPDATE ml SET edeb = mlcredit.ecre
FROM ml INNER JOIN
(SELECT dostamp, ecre FROM ml WHERE RTRIM(conta)= '321') mlcredit ON ml.dostamp = mlcredit.dostamp
WHERE ml.conta = '612' AND YEAR(ml.data)=2010 AND MONTH(ml.data)=1
AND mlcredit.ecre <> ml.edeb
Many thanks for all your suggestions and replies.
Best regards
Luis Santos
June 1, 2010 at 7:31 am
Hello comunity
Nobody have suggestions?
Best regards
Luis Santos
June 1, 2010 at 8:23 am
I looked at this question earlier - but you have made it hard for people to help you - so I chose to move on to other questions.
For advice on how best to post your question to get a great response, please read this short SSC article:
http://www.sqlservercentral.com/articles/61537/
If you can provide the things asked for in there, I'll be happy to look at this again.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 1, 2010 at 11:21 am
Hello Paul
Thanks for your reply, i understand how sometimes is difficult to give an opinion about solving one problem without any data to test, also i build this script for testing and understand better what i need.
note : MLSTAMP is unique by each row of document, but DOSTAMP like i said is the same for all lines for the same documents.
Now , the script to test, where you go to see the DEBITvalue of my account 612 is equal per document (DOSTAMP) with the value od my account 321.
-- 1: CREATING TEMP TABLE
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--===== Create the test table with
CREATE TABLE #mytable
(
MLSTAMP VARCHAR(25),
DOSTAMP VARCHAR(25),
DateValue DATETIME,
Account varchar(15),
DebitValue DECIMAL(10,2),
CreditValue DECIMAL(10,2)
)
--===== Setup any special required conditions especially where dates are concerned
SET DATEFORMAT DMY
--2: INSERTING SOME DATA onTHEM
--===== All Inserts into the IDENTITY column
--SET IDENTITY_INSERT #mytable ON
--===== Insert the test data into the test table
INSERT INTO #mytable
(MLSTAMP, DOSTAMP, DateValue, ACCOUNT, DEBITVALUE, CREDITvalue)
SELECT 'Car09051956696,269000001 ','Car09051956696,237225658','Apr 1 2009 12:00AM','711113','0.000000','1249.350000' UNION ALL
SELECT 'Car09051956696,300000002 ','Car09051956696,237225658','Apr 1 2009 12:00AM','612','500.890000','0.000000' UNION ALL
SELECT 'Car09051956696,316000003 ','Car09051956696,237225658','Apr 1 2009 12:00AM','321','0.000000','784.890000' UNION ALL
SELECT 'Car09051956696,347000004 ','Car09051956696,237225658','Apr 1 2009 12:00AM','2433113','0.000000','249.870000' UNION ALL
SELECT 'Car09051956696,362000005 ','Car09051956696,237225658','Apr 1 2009 12:00AM','21110002447','1499.220000','0.000000' UNION ALL
SELECT 'Car09051956696,753000001 ','Car09051956696,737443702','Apr 1 2009 12:00AM','711113','0.000000','1328.400000' UNION ALL
SELECT 'Car09051956696,769000002 ','Car09051956696,737443702','Apr 1 2009 12:00AM','612','934.100000','0.000000' UNION ALL
SELECT 'Car09051956696,800000003 ','Car09051956696,737443702','Apr 1 2009 12:00AM','321','0.000000','934.090000' UNION ALL
SELECT 'Car09051956696,816000004 ','Car09051956696,737443702','Apr 1 2009 12:00AM','2433113','0.000000','265.680000' UNION ALL
SELECT 'Car09051956696,847000005 ','Car09051956696,737443702','Apr 1 2009 12:00AM','21110002447','1594.080000','0.000000' UNION ALL
SELECT 'Car09051956697,003000001 ','Car09051956696,987237764','Apr 1 2009 12:00AM','711113','0.000000','1396.950000' UNION ALL
SELECT 'Car09051956697,019000002 ','Car09051956696,987237764','Apr 1 2009 12:00AM','612','884.000000','0.000000' UNION ALL
SELECT 'Car09051956697,034000003 ','Car09051956696,987237764','Apr 1 2009 12:00AM','321','0.000000','883.990000' UNION ALL
SELECT 'Car09051956697,066000004 ','Car09051956696,987237764','Apr 1 2009 12:00AM','2433113','0.000000','279.390000' UNION ALL
SELECT 'Car09051956697,097000005 ','Car09051956696,987237764','Apr 1 2009 12:00AM','21110002447','1676.340000','0.000000' UNION ALL
SELECT 'Car09051956697,206000001 ','Car09051956697,191034432','Apr 1 2009 12:00AM','711113','0.000000','1596.600000' UNION ALL
SELECT 'Car09051956697,222000002 ','Car09051956697,191034432','Apr 1 2009 12:00AM','612','1179.000000','0.000000' UNION ALL
SELECT 'Car09051956697,237000003 ','Car09051956697,191034432','Apr 1 2009 12:00AM','321','0.000000','1178.420000' UNION ALL
SELECT 'Car09051956697,253000004 ','Car09051956697,191034432','Apr 1 2009 12:00AM','2433113','0.000000','319.320000' UNION ALL
SELECT 'Car09051956697,269000005 ','Car09051956697,191034432','Apr 1 2009 12:00AM','21110002447','1915.920000','0.000000' UNION ALL
SELECT 'Car09051956697,441000001 ','Car09051956697,441990331','Apr 1 2009 12:00AM','711113','0.000000','1235.700000' UNION ALL
SELECT 'Car09051956697,456000002 ','Car09051956697,441990331','Apr 1 2009 12:00AM','612','788.000000','0.000000' UNION ALL
SELECT 'Car09051956697,487000003 ','Car09051956697,441990331','Apr 1 2009 12:00AM','321','0.000000','788.090000' UNION ALL
SELECT 'Car09051956697,503000004 ','Car09051956697,441990331','Apr 1 2009 12:00AM','2433113','0.000000','247.140000' UNION ALL
SELECT 'Car09051956697,519000005 ','Car09051956697,441990331','Apr 1 2009 12:00AM','21110002447','1482.840000','0.000000' UNION ALL
SELECT 'Car09051956697,644000001 ','Car09051956697,644759430','Apr 1 2009 12:00AM','711113','0.000000','967.200000' UNION ALL
SELECT 'Car09051956697,659000002 ','Car09051956697,644759430','Apr 1 2009 12:00AM','612','679.550000','0.000000' UNION ALL
SELECT 'Car09051956697,691000003 ','Car09051956697,644759430','Apr 1 2009 12:00AM','321','0.000000','679.540000' UNION ALL
SELECT 'Car09051956697,706000004 ','Car09051956697,644759430','Apr 1 2009 12:00AM','2433113','0.000000','193.440000' UNION ALL
SELECT 'Car09051956697,722000005 ','Car09051956697,644759430','Apr 1 2009 12:00AM','21110002447','1160.640000','0.000000'
--===== Set the identity insert back to normal
-- SET IDENTITY_INSERT #mytable ON
Now, i think you have all the information to see what i pretend and to see if my previous TSQL Update script is correct or if exist another better way.
Best regards.
Luis Santos
June 2, 2010 at 2:58 am
Hello Paul
Have you see my last post, sorry if i insist but i need to be sure my query is correct for what iám pretend.
Best regards
Luis Santos
June 2, 2010 at 4:18 am
It *might* be correct and safe if the appropriate uniqueness guarantees exist. In the sample you provided, there is no unique index, constraint, or primary key.
A safer way to write it (but not as efficient) would be:
UPDATE A612
SET DebitValue =
(
SELECT A321.CreditValue
FROM #mytable A321
WHERE A321.Account = '321'
AND A321.DateValue >= '20090401'
AND A321.DateValue < '20090501'
AND A321.DOSTAMP = A612.DOSTAMP
)
FROM #mytable A612
WHERE A612.Account = '612'
AND A612.DateValue >= '20090401'
AND A612.DateValue < '20090501'
AND EXISTS
(
SELECT *
FROM #mytable A321
WHERE A321.Account = '321'
AND A321.DateValue >= '20090401'
AND A321.DateValue < '20090501'
AND A321.DOSTAMP = A612.DOSTAMP
);
...and that's only if I have understood you correctly.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 2, 2010 at 9:21 am
Hello Paul
Thanks again for your kind reply. you are right when you said:
" there is no unique index, constraint, or primary key."
I build my script simplifying the data for testing, but in my table ML my primary key is MLSTAMP and the field DOSTAMP is the primary key of the corresponding header.
i think these constraints are irrelevant in this particular case.
I send to you the printscreen in word2007 for you see better the application and also some explanations.
Sorry, if i insist, but it´s important to me understand correctly if i need such complicate script instead my original script.
Thanks again Paul and give me your suggestion please.
Luis Santos
June 2, 2010 at 12:03 pm
luissantos (6/2/2010)
I build my script simplifying the data for testing, but in my table ML my primary key is MLSTAMP and the field DOSTAMP is the primary key of the corresponding header. i think these constraints are irrelevant in this particular case.
It is fine to simplify the data - but only if the question remains the same.
The reason I mentioned unique constraints is that an UPDATE statement of the type you are intending to use, is only safe if it is guaranteed to only update a particular row once.
If you get into a situation where the same row could be updated to two different values, the UPDATE...FROM syntax can produce non-deterministic results. Since your query is written in a way that does not throw an error if that sort of multiple-update problem occurs, it is not guaranteed to be safe. The only way it could be safe, would be if a unique constraint of some sort existed to avoid the problem in practice.
The code I presented, while certainly less efficient, will throw an error if the multiple-update problem occurs.
Sorry, if i insist, but it´s important to me understand correctly if i need such complicate script instead my original script.
Your original script seems potentially flawed to me. Let me write it out in a more easily readable format:
UPDATE ml
SET edeb = mlcredit.ecre
FROM ml
INNER
JOIN (
SELECT dostamp, ecre
FROM ml
WHERE RTRIM(conta)= '321'
) mlcredit
ON ml.dostamp = mlcredit.dostamp
WHERE ml.conta = '612'
AND YEAR(ml.data)=2010
AND MONTH(ml.data)=1
AND mlcredit.ecre <> ml.edeb
Using the same JOIN idea, but on the sample data you provided, I would write it like so:
UPDATE A612
SET DebitValue = A321.CreditValue
FROM #mytable A612
JOIN (
SELECT A321.CreditValue,
A321.DOSTAMP
FROM #mytable A321
WHERE A321.Account = '321'
AND A321.DateValue >= '20090401'
AND A321.DateValue < '20090501'
) A321
ON A321.DOSTAMP = A612.DOSTAMP
WHERE A612.Account = '612'
AND A612.DateValue >= '20090401'
AND A612.DateValue < '20090501';
There are two main differences. The first one is that the JOIN table contains a restriction on the DateValue column. That is not present in your query, so any row for account 321 (for any date) might be joined to by your query. There is nothing to say that a DOSTAMP is unique to a particular date, so I cannot assume that is the case. This is doubly true if there is nothing in the database to enforce such a condition.
The second difference is that I am using an explicit date range rather than the MONTH and YEAR functions. This is just good practice, since a search on explicit values can seek on a useful index, whereas using MONTH and YEAR pretty much forces the server to perform a full index scan.
Anyway, the critical point here is that you must be absolutely certain that each row to be updated never matches more than one row. Without knowing whether that is guaranteed or not, I would not trust even my rewritten JOINed UPDATE.
Since you cannot provide all the details that would be needed to say whether the JOIN syntax would be safe or not, the only UPDATE that might be safe is the one I provided before (the one with the EXISTS clause). Even then, I cannot be sure that the logic is sound in the context of the business requirement.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 3, 2010 at 8:27 am
Hello again Paul
i send to you in Word 2007, the script for creating my table ML where you can see PK, Constraints...etc.
when you said:
"Anyway, the critical point here is that you must be absolutely certain that each row to be updated never matches more than one row. Without knowing whether that is guaranteed or not, I would not trust even my rewritten JOINed UPDATE."
I understand your preocupation because if in my ML lines table, i have the account 321 repeat more than once, or also the account 612, it´s complicated specify what the correct debitvalue on account 612 based on account 321 i must apply with the UPDATE statment.
But, for the same document (Sale invoice) on my application, i can only use once the account 321 and 612, this account represent the total cost of my sale document.
I hope i help you to see more clearly the problem.
Many thanks again for your useful help and preoccupation to help me resolve this question.
Best regards
Luis Santos
June 4, 2010 at 4:39 am
Hello Paul
My last post responds to their doubts regarding your last forward.
I´am sorry if i insist but i need this script on Monday and I want to make sure that everything goes well.
Many thanks again
Best regards
Luis Santos
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply