How to update column value which is blank

Question

0.00/5 (No votes)

See more:

Hi all

I have a table where parcel value (double) for 2 months data some how didn't saved.so the column is displaying blank.

I want to update those places with 0.00

Please tell me how to do this.
I tried below query but it's not effecting any row.

SQL

update tbl_transaction_master set parcel='0.00' where parcel = ' '

below is sample data

0.00; "5673" ; ; 2
0.00; "2313" ; ;27
0.00; "1254"; ;1635
0.00 ;"4458"; ;1636
0.00; "8796"; ;8
0.00; "3698"; ;9
0.00; "0215"; ;115

Posted 5-Mar-15 23:21pm

Black_Rose

Updated 5-Mar-15 23:35pm

v3

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2015-03-05T23:28:00

Solution 1

The underlying data is (probably) NULL not blank, and in any event you are checking for it being equal to a space which a double value can never contain. You are also trying to assign a string value to a double.

Try

SQL

update tbl_transaction_master set parcel=0.00 where COALESCE(parcel, 0) = 0

[Edit - corrected the bug where I was comparing strings and numerics)

Posted 5-Mar-15 23:28pm

CHill60

Updated 5-Mar-15 23:55pm

v2

Comments

Black_Rose 6-Mar-15 5:29am

Got the below error

ERROR: invalid input syntax for type numeric: ""
LINE 1: ...ction_master set parcel=0.00 where COALESCE(parcel, '') = ''
^

********** Error **********

ERROR: invalid input syntax for type numeric: ""
SQL state: 22P02
Character: 70

CHill60 6-Mar-15 5:35am

Try using parcel=0 instead - postgreSql can be a bit funny with numbers

Black_Rose 6-Mar-15 5:37am

Yes.it worked.
i changed like below.

update tbl_transaction_master set parcel=0 where COALESCE(parcel,'0') = '0'

Black_Rose 6-Mar-15 5:40am

But if it is blank then what to do..

CHill60 6-Mar-15 5:56am

I've actually amended my solution - I was using a mixture of numeric and string in my COALESCE.
A numeric cannot be "blank" it will either be NULL or a number.

Black_Rose 6-Mar-15 5:56am

But if it is blank then what to do..