New answers tagged

4

The principal difficulty here is probably that the alternative value of MemoText you want to use in case the fieldnum=142 value is null is in a different row. It would be much easier if it was just a different column of the same row. That way you could use the pattern suggested by Manu – I mean specifically this part: set <field_to_update> = ...


1

update xrji_:Usernum set DefendantAddress = trim(CASE WHEN MemoText IS NULL OR MemoText = '' THEN <'text data from field 17'> ELSE MemoText END) from eqanswer where eqanswer.entitynum = xrji_:Usernum.DefendantEntityNum and entityrole = 'DEFENDANT' and fieldnum = (CASE ...


-1

You don't specify the DB Manager you use : SQL Server, MySQL, ... ? In SQL Server, you can achieve what you want this way : update <table> set <field_to_update> = ISNULL(<field142>, <field17>) where <condition> ISNULL(a, b) returns the value of "a" if it's not null, otherwise it returns the value of "b".


7

Community Wiki answer for the various links originally left as comments on this popular question How does a surname of Null cause problems in many databases? (Programmers SE) Falsehoods Programmers Believe About Names by Patrick McKenzie Licensed to Bill by David Mikkelson (snopes.com) An example of coding that will cause this issue in the application ...


2

There's plenty of poorly written software, and even frameworks, that would behave oddly with somebody named Null. But let's not forget the human element: perhaps humans seeing the name on screens and printouts and thinking it's a mistake and deleting the person is another likely cause?


0

Building on Paul White's example, to avoid worrying about the following warning without doing a SET ANSI_WARNINGS OFF: Warning: Null value is eliminated by an aggregate or other SET operation. You can do it with SUM and CASE like this: --Set the target table details here DECLARE @Schema sysname = 'dbo', @Table sysname = 'TableName' DECLARE ...


3

An alternative that does not use a cursor: -- Set the target table details here DECLARE @Schema sysname = N'Production', @Table sysname = N'Product', @SQL nvarchar(max) = N'SELECT '; DECLARE @QName sysname = QUOTENAME(@Schema) + N'.' + QUOTENAME(@Table); SET @SQL += ( SELECT NCHAR(13) + NCHAR(9) + QUOTENAME(C.name + ...


1

Yes. But if it's a one-time operation, you can instead hardcode it by dragging the "Columns" label from the Object Explorer in SSMS into the Query window, which will populate the list of columns into your query. You'd only go the dynamic route if there was absolutely no other option (and unwillingness to do updates when the schema changes isn't a valid ...


12

One thing I don't think I've seen mentioned: We aren't just talking about SQL. The name might start/end in the database... but to get there normally involves MULTIPLE channels. Database, Sql, php, html, javascript... Java, C#, VB, Perl, Phython, Ruby, bash, batch, etc, etc, etc... Each of these steps in the pipeline might involve converting data from one ...


18

The article itself includes a link to a Stack Overflow question that demonstrates the problem; it was in a Flex application where the code: currentChild.appendChild("Fred"); would append an element containing the word Fred to an XML document but the code: currentChild.appendChild("null"); would append an empty element, not an element containing the ...


24

There's a good chance that a good chunk of your confusion stems from the journalist's. The article talks about problems using entire application systems, not just databases. Completely reasonable since this is a piece of writing aimed at mass consumption, but technical details are glossed over or misunderstood by the author. Likely a number of these issues ...


4

Relational databases have to accommodate missing or irrelevent values. For example, the list of customers may include a mobile phone number or a gender. What if a corporation wants to become a customer - what gender should it have? A special indicator is used to show a value is missing. This special flag is NULL. So sloppy programming may cause the missing ...


39

I have seen database interfaces (e.g. framework libraries) that return 'null' as a string for null columns. I believe there was a flag that would turn this on or off for debugging. This flag allows developers to easily determine if the empty field was a result of a null value or an empty value. This is a bad setting, especially in production, and would ...


1

Apart from all the issues with NULL confusing developers, NULLs have another very serious drawback: Performance NULL'able columns are a disaster from a performance perspective. Consider integers arithmetic as an example. In a sane world without NULL, it is "easy" to vectorise integer arithmetic in the database engine code using SIMD instructions to perform ...



Top 50 recent answers are included