List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:May 24 2003 1:03am
Subject:Re: Insert into from Select statement
View as plain text  
At 19:06 -0400 5/23/03, David Granger wrote:
>This is my first time using mySQL lists so please be gentle if I am 
>not following proper protocol.
>My problem is with the following SQL script. I'm using mySQL ver. 
>3.23.51 running on Win 98SE and Win XP Pro.
>insert into tblInvoiceDetail (orderID, lineNumber, partID, 
>partDescription, unitCost, extension, orderDate, clientID, quantity, 
>select orderID, lineNumber, partID, workItemDescription, unitCost, 
>(partsNet+xenonNet),  orderDate, clientID, qtyShipped, PSTValue
>from tblClientSummary
>where transaction= 'Sales Order' and Status <> 'Invoiced'
>The script works perfectly on the Win XP machine but does not on the 
>Win98 machine.
>The problem on the 98 system is that the (partsNet+xenonNet) 
>equation in the 'select' statement returns a null value to the 
>extension field in tblInvoiceDetail table, even when there is data 
>in one of the fields in the tblClientSummary table. If I edit the 
>equation to just select either partsNet or xenonNet, with no 
>equation, then the value is inserted.
>I should also point out that partsNet or xenonNet will NEVER have 
>values in both fields. One of the two fields will always be 0.

But 0 *is* a value.  Do you mean that one of the two fields will always be
NULL?  If so, you might want to replace (partsNet+xenonNet) with


COALESCE() returns the first non-NULL values from among its arguments,
or NULL if they are all NULL.

You don't want to use (partsNet+xenonNet) if either of the values are NULL.
Arithmetic involving NULL values always results in a NULL result.

>I will be upgrading both systems to the newest version of mySQL over 
>the weekend, but I thought I would post the question and hope that 
>the problem can be explained or the error of my ways pointed out to 
>me. Thanks in advance for any help.
>Kindest regards,
>Website Design, Management & Hosting
>Database Solutions for Small Business
>Granger HighTech Inc.
>108 Hiltz Avenue
>Toronto, ON, Canada

Paul DuBois
sql, query
Insert into from Select statementDavid Granger24 May
  • Re: Insert into from Select statementPaul DuBois24 May