Styjun

How do I present a future free of gender stereotypes without being jarring or overpowering the narrative?

How do I debug a dependency package? If I have its source code

A quine of sorts

How to stop QGIS from looking for the wrong PostgreSQL host address in an existing workproject?

English idiomatic equivalents of 能骗就骗 (if you can cheat, then cheat)

What was the first science fiction or fantasy multiple choice book?

Active wildlife outside the window- Good or Bad for Cat psychology?

Why didn't Avengers simply jump 5 years back?

What would you need merely the term "collection" for pitches, but not "scale"?

How do I tell my girlfriend she's been buying me books by the wrong author for the last nine months?

Any Tips On Writing Extended Recollection In A Novel

Rear derailleur got caught in the spokes, what could be a root cause

Is this house-rule removing the increased effect of cantrips at higher character levels balanced?

Which high-degree derivatives play an essential role?

Avoiding repetition when using the "snprintf idiom" to write text

Does a lens with a bigger max. aperture focus faster than a lens with a smaller max. aperture?

What is the lowest possible AC?

Have any large aeroplanes been landed — safely and without damage — in locations that they could not be flown away from?

What's the lunar calendar of two moons

How does the 'five minute adventuring day' affect class balance?

Is it possible to alias a column based on the result of a select+where?

Two palindromes are not enough

Are you required to spend hit dice to take a short rest?

"I am [the / an] owner of a bookstore"?

How to keep a self join of a table updated on update of the main table?

Insert into a MySQL table or update if existsis update with left join will lock all MyIsam tables mysql?How do I Update Only a Few Rows at a Time? (Multiple-Table Syntax)How to get the sizes of the tables of a MySQL database?Update to table joined on composite keyThink I need a Self Join?How to do 3 table JOIN in UPDATE query?MySql combining update with an inner join and limitQuery MySQL updating records in a table joining onto a temporary tableHow do I update fields in a first table using inner join to a second table where field in first table is null?

1

1

I have an update statement with a self join shown in the code below. Am supposed to get values from updated columns but I notice that the update on a1 does not reflect on joined a2.

UPDATE test.cashbook a1 
inner join test.cashbook a2 on a2.id = (CASE WHEN a1.id>1 then a1.id-1 
END)
SET a1.balanceBroughtFoward = a2.balanceCarriedDown, 
 a1.totalCash = a2.balanceCarriedDown + ifnull(a1.cashSale, 0), 
 a1.balanceCarriedDown = ifnull(a1.totalCash, 0) -(ifnull(a1.Lodgement, 0) + ifnull(a1.Expenses, 0))
 where a1.id > 1

The result I expect is to have the previous row's balanceCarriedFoward as my current row balanceBroughtFoward.

This perhaps is the need for the self join to same table as a2 but because only the first row (a1.id = 1) has a balancecarriedFoward before the join, therefore only row two of a1 is updated though on subsequent rows of the original table (a1) the update command updates balanceCarriedFoward during the update but I guess this values are not reflected in the self joined table (a2) therefore giving null values for a1.balancbroughtFoward where row is > 2.

How can I get around this since I am limited to using MySQL 5.6?

mysql

share|improve this question

edited Mar 25 at 19:54

Ikem Chinonso

asked Mar 25 at 16:30

Ikem ChinonsoIkem Chinonso

633 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Have you tried this query with SELECT?
  
  – tcadidot0
  Mar 26 at 7:17
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Yes, but I can't select from the target table for update which is a1 as a2 wouldn't have the updated values. Another approach would have been to put the join in the select from a2 but mysql doesn't accept join after set clause but rather after update clause.
  
  – Ikem Chinonso
  Mar 26 at 7:26
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I'm sorry @Ikem Chinonso, could you please run this query and post the result here? SELECT a1.balanceBroughtFoward,a2.balanceCarriedDown, a1.totalCash,a2.balanceCarriedDown+IFNULL(a1.cashSale, 0), a1.balanceCarriedDown,IFNULL(a1.totalCash, 0) -(IFNULL(a1.Lodgement, 0) + IFNULL(a1.Expenses, 0)) FROM test.cashbook a1 INNER JOIN test.cashbook a2 ON a2.id=(CASE WHEN a1.id>1 THEN a1.id-1 END) WHERE a1.id > 1; . Thanks
  
  – tcadidot0
  Mar 26 at 7:51
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Ok, no problem.
  
  – Ikem Chinonso
  Mar 26 at 8:14
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks @tcadidot0 I was able to achieve what I wanted with the use of variables. I'll post the code below.
  
  – Ikem Chinonso
  Mar 26 at 11:00
  

  
  
  
  

add a comment | 

1

1

I have an update statement with a self join shown in the code below. Am supposed to get values from updated columns but I notice that the update on a1 does not reflect on joined a2.

UPDATE test.cashbook a1 
inner join test.cashbook a2 on a2.id = (CASE WHEN a1.id>1 then a1.id-1 
END)
SET a1.balanceBroughtFoward = a2.balanceCarriedDown, 
 a1.totalCash = a2.balanceCarriedDown + ifnull(a1.cashSale, 0), 
 a1.balanceCarriedDown = ifnull(a1.totalCash, 0) -(ifnull(a1.Lodgement, 0) + ifnull(a1.Expenses, 0))
 where a1.id > 1

The result I expect is to have the previous row's balanceCarriedFoward as my current row balanceBroughtFoward.

This perhaps is the need for the self join to same table as a2 but because only the first row (a1.id = 1) has a balancecarriedFoward before the join, therefore only row two of a1 is updated though on subsequent rows of the original table (a1) the update command updates balanceCarriedFoward during the update but I guess this values are not reflected in the self joined table (a2) therefore giving null values for a1.balancbroughtFoward where row is > 2.

How can I get around this since I am limited to using MySQL 5.6?

mysql

share|improve this question

edited Mar 25 at 19:54

Ikem Chinonso

asked Mar 25 at 16:30

Ikem ChinonsoIkem Chinonso

633 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Have you tried this query with SELECT?
  
  – tcadidot0
  Mar 26 at 7:17
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Yes, but I can't select from the target table for update which is a1 as a2 wouldn't have the updated values. Another approach would have been to put the join in the select from a2 but mysql doesn't accept join after set clause but rather after update clause.
  
  – Ikem Chinonso
  Mar 26 at 7:26
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I'm sorry @Ikem Chinonso, could you please run this query and post the result here? SELECT a1.balanceBroughtFoward,a2.balanceCarriedDown, a1.totalCash,a2.balanceCarriedDown+IFNULL(a1.cashSale, 0), a1.balanceCarriedDown,IFNULL(a1.totalCash, 0) -(IFNULL(a1.Lodgement, 0) + IFNULL(a1.Expenses, 0)) FROM test.cashbook a1 INNER JOIN test.cashbook a2 ON a2.id=(CASE WHEN a1.id>1 THEN a1.id-1 END) WHERE a1.id > 1; . Thanks
  
  – tcadidot0
  Mar 26 at 7:51
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Ok, no problem.
  
  – Ikem Chinonso
  Mar 26 at 8:14
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks @tcadidot0 I was able to achieve what I wanted with the use of variables. I'll post the code below.
  
  – Ikem Chinonso
  Mar 26 at 11:00
  

  
  
  
  

add a comment | 

I have an update statement with a self join shown in the code below. Am supposed to get values from updated columns but I notice that the update on a1 does not reflect on joined a2.

UPDATE test.cashbook a1 
inner join test.cashbook a2 on a2.id = (CASE WHEN a1.id>1 then a1.id-1 
END)
SET a1.balanceBroughtFoward = a2.balanceCarriedDown, 
 a1.totalCash = a2.balanceCarriedDown + ifnull(a1.cashSale, 0), 
 a1.balanceCarriedDown = ifnull(a1.totalCash, 0) -(ifnull(a1.Lodgement, 0) + ifnull(a1.Expenses, 0))
 where a1.id > 1

The result I expect is to have the previous row's balanceCarriedFoward as my current row balanceBroughtFoward.

This perhaps is the need for the self join to same table as a2 but because only the first row (a1.id = 1) has a balancecarriedFoward before the join, therefore only row two of a1 is updated though on subsequent rows of the original table (a1) the update command updates balanceCarriedFoward during the update but I guess this values are not reflected in the self joined table (a2) therefore giving null values for a1.balancbroughtFoward where row is > 2.

How can I get around this since I am limited to using MySQL 5.6?

mysql

share|improve this question

edited Mar 25 at 19:54

Ikem Chinonso

asked Mar 25 at 16:30

Ikem ChinonsoIkem Chinonso

633 bronze badges

UPDATE test.cashbook a1 
inner join test.cashbook a2 on a2.id = (CASE WHEN a1.id>1 then a1.id-1 
END)
SET a1.balanceBroughtFoward = a2.balanceCarriedDown, 
 a1.totalCash = a2.balanceCarriedDown + ifnull(a1.cashSale, 0), 
 a1.balanceCarriedDown = ifnull(a1.totalCash, 0) -(ifnull(a1.Lodgement, 0) + ifnull(a1.Expenses, 0))
 where a1.id > 1

share|improve this question

edited Mar 25 at 19:54

Ikem Chinonso

asked Mar 25 at 16:30

Ikem ChinonsoIkem Chinonso

633 bronze badges

share|improve this question

edited Mar 25 at 19:54

Ikem Chinonso

asked Mar 25 at 16:30

Ikem ChinonsoIkem Chinonso

633 bronze badges

asked Mar 25 at 16:30

Ikem ChinonsoIkem Chinonso

633 bronze badges

asked Mar 25 at 16:30

Ikem ChinonsoIkem Chinonso

633 bronze badges

1 Answer
1

active

oldest

votes

0

I used a variable to achieve the need of joining a2 to a1. Code below.

SET bc:= 0
UPDATE test.cashbook
Set balanceBroughtFoward = bc, balanceCarriedFoward = (bc:= totalCash - (Lodgement + Expenses) )

The above code worked but then I now want to put the update statement in a trigger. it gives syntax error when I try. Am stock at this point.

share|improve this answer

answered Mar 26 at 21:28

Ikem ChinonsoIkem Chinonso

633 bronze badges

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55342407%2fhow-to-keep-a-self-join-of-a-table-updated-on-update-of-the-main-table%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

I used a variable to achieve the need of joining a2 to a1. Code below.

SET bc:= 0
UPDATE test.cashbook
Set balanceBroughtFoward = bc, balanceCarriedFoward = (bc:= totalCash - (Lodgement + Expenses) )

The above code worked but then I now want to put the update statement in a trigger. it gives syntax error when I try. Am stock at this point.

share|improve this answer

answered Mar 26 at 21:28

Ikem ChinonsoIkem Chinonso

633 bronze badges

add a comment | 

0

I used a variable to achieve the need of joining a2 to a1. Code below.

SET bc:= 0
UPDATE test.cashbook
Set balanceBroughtFoward = bc, balanceCarriedFoward = (bc:= totalCash - (Lodgement + Expenses) )

The above code worked but then I now want to put the update statement in a trigger. it gives syntax error when I try. Am stock at this point.

share|improve this answer

answered Mar 26 at 21:28

Ikem ChinonsoIkem Chinonso

633 bronze badges

add a comment | 

I used a variable to achieve the need of joining a2 to a1. Code below.

SET bc:= 0
UPDATE test.cashbook
Set balanceBroughtFoward = bc, balanceCarriedFoward = (bc:= totalCash - (Lodgement + Expenses) )

The above code worked but then I now want to put the update statement in a trigger. it gives syntax error when I try. Am stock at this point.

share|improve this answer

answered Mar 26 at 21:28

Ikem ChinonsoIkem Chinonso

633 bronze badges

share|improve this answer

answered Mar 26 at 21:28

Ikem ChinonsoIkem Chinonso

633 bronze badges

answered Mar 26 at 21:28

Ikem ChinonsoIkem Chinonso

633 bronze badges

answered Mar 26 at 21:28

Ikem ChinonsoIkem Chinonso

633 bronze badges