Help with Update from Child table  
Author Message
Lyfe





PostPosted: Fri Nov 11 09:56:05 CST 2005 Top

SQL Server Developer >> Help with Update from Child table

I have two tables tblInvoice and tblInvoice Line.

Both tables have a column InvoiceID that is Primary / Foreign Key .

tblInvoice has a column TransType char(2) 'SI' or 'SC'

I have now added this column to tblInvoice and want to update the
values with the ones from tblInvoiceLine where the InvoiceID matches
before deleting the column from tblInvoiceLine.

tblInvoiceLine has many records for each invoice but all are the same
transaction type so any will do.

Can sonmeone show how to write this update?

Thanks
Hals_left

SQL Server271  
 
 
AbsarAhmad





PostPosted: Fri Nov 11 09:56:05 CST 2005 Top

SQL Server Developer >> Help with Update from Child table This script should do the work.

update tblInvoice
set TransType = b.TransType
from tblInvoice a join
(select distinct InvoiceID, TransType from tblInvoiceLine) b
on a.invoiceid = b.invoiceid



> I have two tables tblInvoice and tblInvoice Line.
>
> Both tables have a column InvoiceID that is Primary / Foreign Key .
>
> tblInvoice has a column TransType char(2) 'SI' or 'SC'
>
> I have now added this column to tblInvoice and want to update the
> values with the ones from tblInvoiceLine where the InvoiceID matches
> before deleting the column from tblInvoiceLine.
>
> tblInvoiceLine has many records for each invoice but all are the same
> transaction type so any will do.
>
> Can sonmeone show how to write this update?
>
> Thanks
> Hals_left
>
>
 
 
Hugo





PostPosted: Fri Nov 11 17:28:51 CST 2005 Top

SQL Server Developer >> Help with Update from Child table

>I have two tables tblInvoice and tblInvoice Line.
>
>Both tables have a column InvoiceID that is Primary / Foreign Key .
>
>tblInvoice has a column TransType char(2) 'SI' or 'SC'
>
>I have now added this column to tblInvoice and want to update the
>values with the ones from tblInvoiceLine where the InvoiceID matches
>before deleting the column from tblInvoiceLine.
>
>tblInvoiceLine has many records for each invoice but all are the same
>transaction type so any will do.
>
>Can sonmeone show how to write this update?
>
>Thanks
>Hals_left

Hi Hals_left,

Absar already posted a solution, but beware - if for some Invoice, the
Invoice lines do have different trnasaction type, it will just pick one
at random, without giving an error or even a warning.

The version below will fail (with an error message) if there are
invoices with more than one transactiontype, allowing you to check and
fix the problem before proceeding:

UPDATE Invoice
SET TransType = (SELECT b.TransType
FROM InvoiceLine AS b
WHERE b.InvoiceID = Invoice.InvoiceID)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
 
 
--CELKO--





PostPosted: Fri Nov 11 20:08:06 CST 2005 Top

SQL Server Developer >> Help with Update from Child table Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it. If you were polite would we have posted something like:

CREATE TABLE Invoices -- assumes ther is more than one!!
(inv_nbr INTEGER NOT NULL PRIMARY KEY,
trans_type CHAR(2) NOT NULL
CHECK (trans_type IN ('SI', 'SC')),
..);

Never put that silly redundant "tbl-" prefix on names. You did not
know that you sell items and NOT the PHYSICAL lines on an order form.
Newbies often confuse the PAPER FORMS with a logical data elementr like
that.

CREATE TABLE InvoiceDetails
(inv_nbr INTEGER NOT NULL
REFERENCES Invoices(inv_nbr)
ON DELETE CASCADE
ON UPDATE CASCADE,
upc CHAR(13) NOT NULL
REFERENCES Inventory(upc)
ON UPDATE CASCADE,
item_qty INTEGER NOT NULL
CHECK (item_qty > 0),
..);

>> has a column trans_type CHAR(2) 'SI' or 'SC'. I have now added this column to lnvoices and want to update the values with the ones from InvoiceDetails where the invoice_id matches before deleting the column from InvoiceDetails. <<

So I have an order with two details; one is 'SI' and one is 'SC';
which one goes into the Invoices table? Do not assume that all details
will have the trans_type.

Based on 20+ years of writing SQL standards and cleaning up bad design,
I think your DDL is screwed up beyond repair. You need a full review
and rules for scrubbing the data.