Update Blank Fields In MySQL

Milti has asked for the wisdom of the Perl Monks concerning the following question:

Say I have a table with 5 fields. The first INSERT into the table requires and permits only the insertion of data into fields 1,2, and 3. At a later time a user might want to ad data in fields 4 and 5. If those fields are set to NULL the UPDATE query does not seem to work, apparently because there is nothing in those fields to update. There must be some way around this problem that I am missing. If I set the fields to NOT NULL and attempt to make the insertion in to fields 1,2, and 3, the program stops and I get an error saying field 4 doesn't have a default value (mysql shows that it does, i.e. NULL). The data type in all the fields is varchar. So what do I do? Any help will be greatly appreciated.

Comment on Update Blank Fields In MySQL

Replies are listed 'Best First'.
Re: Update Blank Fields In MySQL by mbethke (Hermit) on Feb 05, 2014 at 22:11 UTC
So what do I do? Just show your code. It sounds more like a problem with SQL than with Perl but I suppose it's not very complicated in any case.	[reply]
Re: Update Blank Fields In MySQL by Anonymous Monk on Feb 06, 2014 at 10:27 UTC
This is nothing but a guess, but If those fields are set to NULL the UPDATE query does not seem to work, apparently because there is nothing in those fields to update. What's the query? `"WHERE field4 = NULL"`? Not going to work. `"WHERE field4 IS NULL"` will.	[reply] [d/l] [select]
Re: Update Blank Fields In MySQL by Jim (Curate) on Feb 06, 2014 at 00:00 UTC
There's an extraneous left parenthesis on line 23 of your Perl script. If fixing that bug doesn't help, try this. Jim	[reply]
Re: Update Blank Fields In MySQL by mendeepak (Scribe) on Feb 07, 2014 at 06:58 UTC
set a default value for your fields and as others told you it sounds more like SQL problem than Perl. FYI : whenever you write a question please provide what you have tried with your code Deepak	[reply]
Re^2: Update Blank Fields In MySQL by Anonymous Monk on Feb 07, 2014 at 07:47 UTC
`NULL` is a very good default value, you know. Don't be afraid of them.	[reply] [d/l]
Re^3: Update Blank Fields In MySQL by Milti (Beadle) on Feb 22, 2014 at 18:02 UTC
Thanks! I set the default values on each column and used placeholders with the query. The problem has been solved!!! Many thanks to all who responded.	[reply]