Re: Insert, Delete, and Update - best practices advice please
From: Mike Perry (_at_)
Date: 07/27/04
- Next message: Hugo Kornelis: "Re: using a trigger to enforce referential integrity"
- Previous message: David Portas: "Re: Date Ranges"
- In reply to: David Portas: "Re: Insert, Delete, and Update - best practices advice please"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 27 Jul 2004 16:38:32 -0500
Just FYI:
Your UPDATE portion of the Query did not work, got more than one answer from
each of the SELECT statements for each COLUMN to be updated.
I believe that I have the solution with the following:
UPDATE TargetTable
SET ColA = SourceTable.ColA,
ColB = SourceTable.ColB,
ColC = SourceTable.ColC
...
FROM SourceTable
WHERE (TargetTable.KeyColA = SourceTable.KeyColA
AND TargetTable.KeyColB = SourceTable.KeyColB)
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@acm.org> wrote in message
news:rtudnX5Y34EGGZzcRVn-ug@giganews.com...
> First things first. Your table design - you haven't finished it. Despite
> what you say about (Invoice_Nbr,Line_Nbr) being unique those columns are
> nullable, as are all the columns, so the table doesn't have a primary key
at
> all. A "Line Number" is anyway a strange concept to record in a database.
I
> would have expected the key to be (Invoice_Nbr, Item_Nbr). A Line Number
is
> surely meaningful only on a printed invoice.
>
> It also seems that this table isn't in Third Normal Form. Do you really
have
> different customers and dates on the same invoice? Price_Extension is
> presumably a derived column that doesn't belong at all.
>
> My point is that what you have created is a "report" or "view" of your
data
> and actually to materialize that as a table is probably unnecessary, weak
> design and creates the headache that you are now facing of how to keep it
up
> to date. Have you considered creating a view to return the data in this
> format? Presumably your temp table would be populated from some query that
> already returns the data like this?
>
> Assuming you did want to maintain this data as a table, I don't understand
> why you would use a temp table to do it. That just creates an extra step
to
> move around even more redundant data. You haven't shown us how this Temp
> Table gets populated so I can only give a generic example of how you can
> replicate changes between two similar tables:
>
> DELETE FROM TargetTable
> WHERE NOT EXISTS
> (SELECT *
> FROM SourceTable
> WHERE keycol = TargetTable.keycol)
>
> UPDATE TargetTable
> SET
> col1 = (SELECT col1 FROM SourceTable WHERE keycol = TargetTable.keycol),
> col2 = (SELECT col2 FROM SourceTable WHERE keycol = TargetTable.keycol),
> col3 = (SELECT col3 FROM SourceTable WHERE keycol = TargetTable.keycol),
> ...
>
> INSERT INTO TargetTable (keycol,col1,col2,col3, ...)
> SELECT S.keycol, S.col1, S.col2, S.col3, ...
> FROM SourceTable AS S
> LEFT JOIN TargetTable AS T ON S.keycol = T.keycol
> WHERE T.keycol IS NULL
>
> Hope this helps.
>
> --
> David Portas
> SQL Server MVP
> --
>
>
- Next message: Hugo Kornelis: "Re: using a trigger to enforce referential integrity"
- Previous message: David Portas: "Re: Date Ranges"
- In reply to: David Portas: "Re: Insert, Delete, and Update - best practices advice please"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
