Base Update Query Trouble

[gkick]

Hi,

I have a cashflow table which contains 2 decimal fields both set to default 0.
I also have a Credit/DebitCardExpenses table for card transactions. Once the table closes I run an update routine to dump the sum for the next cardbill into the cashflow table. That all works fine with the exception that the update statement does the update correct but also wipes the o s in unrelated rows ???

This is the code

UPDATE “tblcashflow” SET “debit”= (SELECT “vBillMonthTotals”.“cmytotal”
FROM “vBillMonthTotals” WHERE “vBillMonthTotals”.“cmy” = “tblcashflow”.“cmy” )

Below a before and an after update screendump

Hmm seem to have trouble attaching the images, default to http

Anyway is there a way to stop the code to erase the nulls?

Thank you

before.PNG

updatekillsdefaults.PNG

[Ratslinger]

Hello,

Just tested you question to add an image - no problem. Believe you are using a wrong icon to insert image - link?

Need something. The information is not enough. Have no idea what your fields even are.

Edit: Most likely solution is in answer here → Problems updating one table from another

[gkick]

@Ratslinger, thanks indeed I used the wrong buttton, grrr… Have edited the post, fields in questions are credit decimal, debit decimal, cmy integer in both tables.

[Ratslinger]

Hello,

This is based upon the answer noted in my comment:

UPDATE "tblcashflow" SET "debit"= (SELECT "vBillMonthTotals"."cmytotal" FROM "vBillMonthTotals" WHERE "vBillMonthTotals"."cmy" = "tblcashflow"."cmy" ) where 1 in (select 1 from "vBillMonthTotals" where "vBillMonthTotals"."cmy" = "tblcashflow"."cmy")

Quick/rough test updated correctly without NULL insertion.

[gkick]

Many thanks for that, past midnight here, whats the time in your neck of the woods ?

[Ratslinger]

You are welcome. Appears 3 hours behind your time.