update invoices
set payment_total = invoice_total * 0.5, payment_date = due_date
where invoice_id = 3
I wrote the code exactly as taught.
but why it’s giving me a warning ?
16:33:20 update invoices set payment_total = invoice_total * 0.5, payment_date = due_date where invoice_id = 3 0 row(s) affected, 1 warning(s): 1265 Data truncated for column ‘payment_total’ at row 1 Rows matched: 1 Changed: 0 Warnings: 1 0.000 sec
The error message says it: At least one of the computed values had more digits than the column can hold so the value was truncated to fit.
Say we have an invoice_total of 100.01. Multiplying that by 0.5 gives us 50.005. The three decimal places do not fit into a column with two decimal places.
In real world scenarios you should pay attention to such warnings since truncation often isn’t what you want. Instead you might want to apply some rounding.
ah I see.
So what should I do in this case.
Thank you so much ^ ^
W
In this case - nothing. It’s just an example.
In real world scenarios you would check the business rules on how to handle a situation like this and add an explicit truncation or rounding.
But I canNOT execute the code?
Can you clarify your question?
WHAT??! I said I cannot execute the bloody code but It WORKED in Mosh’s SQL platform!!!
You’re not making any sense at all.
What you DONT UNDERSTAND WHAT I CANT EXECUTE THE CODE
What you DONT UNDERSTAND WHAT I CANT EXECUTE THE CODE AND IT THROWS AN ERROR MEANS??
IF YOU DON’T KNOW HOW TO RESOLVE SAY IT.DONT GIVE ME CRAP. ,
If your MySQL server is in strict mode you can use UPDATE IGNORE or need to explicitly apply truncation or rounding where necessary. To achieve the same result as Mosh did, use ROUND(invoice_total * 0.5, 2).
Well, I search about it and I can say that to fix this issue, you can check the information types and section sizes of the ‘payment_total’ and ‘invoice_total’ segments in the ‘invoice’ table. Ensure that the ‘payment_total’ column can accommodate the calculated value without truncation. You might need to adjust the data type or increase the column size to store the calculated payment total accurately.
I hope it will give you some idea.
Thanks
Of course such a warning can give you hints that your data types are not correct. But that’s really depending on the problem domain.
In this case the columns contain amounts of money and have two decimal digits. They are designed for amounts with dollars and cents. It wouldn’t make much sense to extent a payment total column to more than two decimal digits.
THIS does NOT work unfort
why the hell the simple code not work
Hi Molly,
Its because invoice_total * 0.5 is $73.995 which is 3 decimal places but we can only fit data with two decimal places in that column so the warning was displayed to say the value returned was shorted to fit 2 decimal places there. This is a warning, it does not affect if the operation actually ran or not, this is different to an error in which case the operation will not run at all. Because this is a tutorial you can usually ignore such warnings as data integrity is not so important here rather the underlying concepts.
If you would like to resolve the warning just add another digit after the decimal places, so put a 3 instead of two for the data type. ie: instead of DECIMAL(9,2) write DECIMAL(9,3) and apply to the payment_total column and do it to the invoice_total also for consistency. screenshots below. However, this may cause some data inconsistencies as your setup will be different to mosh’s so as Sam mentioned the best way would be to use the ROUND function, then you wont get the warning, so your sql query would look like:
update invoices
set payment_total = ROUND(invoice_total * 0.5, 2), payment_date = due_date
where invoice_id = 3
Hi thanks
When I clicked the tool button after invoices, I get this: (not the table you shown there):
I think sb did some bad things!! fking hell
no one did bad things. other code work.
apolog
