Need help with a Conditional Insert or Update statement

Question

Post reply

Need help with a Conditional Insert or Update statement

John-79567

SSCommitted

Points: 1892
More actions
June 27, 2006 at 1:05 pm

#118519

The tricky part is that i need to cascade this to the child tables.
pseudo code:
If records exists in table1
Begin
     Update table1.*
    If table1.ID exist in table2.fID
        Begin
          Update table2.* from table1.updaterecords
        End
     Else
         Begin
             Insert into table2.* from table1.NewRecord
        End
End
Else
   Begin
       insert into table1
       If table1.ID exists in table2.fID
          Begin
               Update -- new record id from previous insert
           End
        Else
           begin
               Insert into table2 -- using table1.ID
           end
       Else
           Begin
               Insert into table2 -- using table1.ID
           End
      End
End

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

stax68 SSChampion Points: 11711 More actions · Answer 1

Based on what i can deduce about your requirement, and if this is for single rows only, this would do it:

pseudocode:

update table1

if rowcount is 0, insert table1

update table2

if rowcount is 0, insert table2

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

sushila SSC-Dedicated Points: 35293 More actions · Answer 2

hmm...my understanding is more like this:

psuedocode...

if exists(select * from table1 where....)
   begin
   update table1....
if exists(select * from table1 join table2 on table1.ID = table2.fID)
begin
     update table2...
end
   end
else
   begin
insert into table1...
if exists(select * from table1 join table2 on table1.ID = table2.fID)
    begin
        update table2...
    end
else
    begin
insert into table2.....
    end
   end

..except I can't understand how in the 2nd part when an insert is done into table1 there's a possibility that this new ID already exists in table2....isn't this a PK/FK relationship ?!?!?!

...but then again, my interpretation could be totally wrong

**ASCII stupid question, get a stupid ANSI !!!**

stax68 SSChampion Points: 11711 More actions · Answer 3

Yes, I was wondering about that. At the point in time at which you insert a new record, there oughtn't to be any records referencing it - what did they reference before it existed. I assumed that this was redundant code. Further info on whether these answers meet your requirement, or a description of what you want to do would be useful.

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant