Update

Update

Post by sardink » Thu, 12 Jun 2003 15:38:37



I have 2 tables. I need to insert desc from Table2 into
Table1 if the desc is not exists in Table1 and then
update table2 with the appropriate desc_ID from Table1.
In Table1 I have identity set to yes (seed=1 ) on column
desc_ID.
How do I do this?

CREATE TABLE [dbo].[Table1] (
        [site] [tinyint] NOT NULL ,
        [desc_ID] [int] NOT NULL ,
        [desc] [varchar] (50)

) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Table2] (
        [Site] [tinyint] NULL ,
        [desc_ID] [int] NULL ,
        [desc] [varchar] (30)

) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Table1] WITH NOCHECK ADD
        CONSTRAINT [pk_key] PRIMARY KEY  CLUSTERED
        (
                [ID],
                [desc_ID]
        ) WITH  FILLFACTOR = 90  ON [PRIMARY]
GO

 
 
 

Update

Post by Anith Se » Thu, 12 Jun 2003 17:19:58


Since you will have to get the identity value for each row, you will have to

(or SCOPE_IDENTITY() in SQL 2000). This is a drawback of using identity
columns in tables when references becomes a problem during bulk insert
operations like this. Another option is, if you have unique values for the
column "desc", you can use that column to formulate your joins.

On another note, is this a data cleanup job? Why do you have two tables with
the exact same schema in your database?

--
- Anith
( Please reply to newsgroups only )

 
 
 

Update

Post by Max Headroo » Tue, 17 Jun 2003 08:53:24


Hello,
I'm having some problems with this update statement. I'm trying to update
all these rows

(SELECT
b.JPP_WELSE
FROM OH a LEFT JOIN JPP b
ON a.OH_IDNR = b.JPP_OH_IDNR
WHERE
a.OH_MANDANT = '22' AND
a.OH_OTYP_TYP = 'JOBP' AND
b.JPP_WELSE = 'H')

with this value:

UPDATE jpp
SET jpp_welse = 'A'

The syntax of the update statement is a little bit confusing (C. Use the
UPDATE statement using information from another table). I greatly appreciate
your help.
Regards
Max

 
 
 

Update

Post by Uri Diman » Tue, 17 Jun 2003 10:08:06


Max
(untested)

UPDATE jpp
SET jpp_welse = b.JPP_WELSE
FROM OH a LEFT JOIN JPP b
ON a.OH_IDNR = b.JPP_OH_IDNR
WHERE
a.OH_MANDANT = '22' AND
a.OH_OTYP_TYP = 'JOBP' AND
b.JPP_WELSE = 'H'


Quote:> Hello,
> I'm having some problems with this update statement. I'm trying to update
> all these rows

> (SELECT
> b.JPP_WELSE
> FROM OH a LEFT JOIN JPP b
> ON a.OH_IDNR = b.JPP_OH_IDNR
> WHERE
> a.OH_MANDANT = '22' AND
> a.OH_OTYP_TYP = 'JOBP' AND
> b.JPP_WELSE = 'H')

> with this value:

> UPDATE jpp
> SET jpp_welse = 'A'

> The syntax of the update statement is a little bit confusing (C. Use the
> UPDATE statement using information from another table). I greatly
appreciate
> your help.
> Regards
> Max

 
 
 

Update

Post by Max Headroo » Tue, 17 Jun 2003 13:26:15


Hello Uri,
Will this statement update b.JPP_WELSE with a value of 'A' ?

> Max
> (untested)

> UPDATE jpp
> SET jpp_welse = b.JPP_WELSE
> FROM OH a LEFT JOIN JPP b
> ON a.OH_IDNR = b.JPP_OH_IDNR
> WHERE
> a.OH_MANDANT = '22' AND
> a.OH_OTYP_TYP = 'JOBP' AND
> b.JPP_WELSE = 'H'



> > Hello,
> > I'm having some problems with this update statement. I'm trying to
update
> > all these rows

> > (SELECT
> > b.JPP_WELSE
> > FROM OH a LEFT JOIN JPP b
> > ON a.OH_IDNR = b.JPP_OH_IDNR
> > WHERE
> > a.OH_MANDANT = '22' AND
> > a.OH_OTYP_TYP = 'JOBP' AND
> > b.JPP_WELSE = 'H')

> > with this value:

> > UPDATE jpp
> > SET jpp_welse = 'A'

> > The syntax of the update statement is a little bit confusing (C. Use the
> > UPDATE statement using information from another table). I greatly
> appreciate
> > your help.
> > Regards
> > Max

 
 
 

Update

Post by Uri Diman » Tue, 17 Jun 2003 16:58:13


Max
(untested)

UPDATE jpp
SET jpp_welse = 'A'
FROM OH a LEFT JOIN JPP b
ON a.OH_IDNR = b.JPP_OH_IDNR
WHERE
a.OH_MANDANT = '22' AND
a.OH_OTYP_TYP = 'JOBP' AND
b.JPP_WELSE = 'H'


> Hello Uri,
> Will this statement update b.JPP_WELSE with a value of 'A' ?


> > Max
> > (untested)

> > UPDATE jpp
> > SET jpp_welse = b.JPP_WELSE
> > FROM OH a LEFT JOIN JPP b
> > ON a.OH_IDNR = b.JPP_OH_IDNR
> > WHERE
> > a.OH_MANDANT = '22' AND
> > a.OH_OTYP_TYP = 'JOBP' AND
> > b.JPP_WELSE = 'H'



> > > Hello,
> > > I'm having some problems with this update statement. I'm trying to
> update
> > > all these rows

> > > (SELECT
> > > b.JPP_WELSE
> > > FROM OH a LEFT JOIN JPP b
> > > ON a.OH_IDNR = b.JPP_OH_IDNR
> > > WHERE
> > > a.OH_MANDANT = '22' AND
> > > a.OH_OTYP_TYP = 'JOBP' AND
> > > b.JPP_WELSE = 'H')

> > > with this value:

> > > UPDATE jpp
> > > SET jpp_welse = 'A'

> > > The syntax of the update statement is a little bit confusing (C. Use
the
> > > UPDATE statement using information from another table). I greatly
> > appreciate
> > > your help.
> > > Regards
> > > Max

 
 
 

1. puzzling issue with inserted, updated and IF UPDATE(column) in an update trigger

Here is my situation:

I have a "master" table that has the following PK

Visit    decimal(5,2)
Test    integer

I have a "detail" table with this PK

Subject    integer
Visit        decimal(5,2)
Test        integer

I cannot have enforced referential integrity between these tables, but I
need to be able to cascade the update of "Test" from the master to certain
records in the detail table.
This presents no problem in a single-record update in the master.  But when
I have a multiple record update, because I have altered the primary key of
master, I can't figure out how (or whether) I can relate records in the
deleted table to the inserted table.  BOL says that the records in deleted
won't normally have matching records in the trigger table, and that inserted
is a copy of the new records in the trigger table.  Unless there is a
"bookmark" or recnum that can help me relate deleted to inserted, I'm
looking at coding the two-step process of 1) killing the detail records that
match the key in deleted and 2) inserting the records from inserted that
aren't found in detail.  Can anyone confirm that this is my only option (for
a multi-row update where the PK changes)?

I'm trying to get more info on the IF UPDATE(column) to see if anything can
be done here.  I'm assuming at this point that the UPDATE(column) will be
true for every record in deleted, even if the value was not really changed.
For example, "UPDATE Addresses SET State = 'OH'" would update every record
in Addresses, even those whose State was already "OH".  So IF UPDATE(column)
is not something I can test on a row-by-row basis, to see if the value in
the column was truly changed.  Again, can anyone confirm?

TIA,

Joe

2. Oracle9i ANSI join examples - INNER, OUTER, FULL OUTER

3. Cached Updates QUESTION: Sorting table of updated and non-updated records

4. Printing in Data Report

5. SQL Server update error: row cannot be update for updating

6. Object Owner

7. update trigger > reuse values in update clause

8. Application Log & tempdb

9. Why does Update with join force deferred update?

10. update table w/o updating transaction log?

11. Update reverses to a previous record before the update

12. IF UPDATE clause not working in an Update Trigger

13. Update a field with time stamp when other field are updated