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

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

Show quoteHide quote

On 11 Nov 2005 06:53:57 -0800, hals_left wrote:

Show quoteHide quoteHi 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

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),
..);

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.

Automate Importing File
concurrency in stored proc
Cursor working in this stored Procedure??
Best solution, iterate over millions records and call extended sp
convert INT