Simple set based syntax question 1

[Nevermoor]

Hi,

I am fairly new to SQL and would like to figure out set based syntax, as I am currently assigned to write the following procedure:

Tables used: pipes, nodes, pipe_attributes

Each sewer pipe has a unique id of "NODE-NODE" (ex. 123-456)
The procedure will insert a new node into the pipe and create two new pipes: 123-789 and 789-456.

I can easily do this in the pipe and node databases, however the last step is that for every pipe_attribute with the id "123-456" (There will usually be quite a few) I need to create two copies with the new ids.

I would like to do this with two select into statements, but I can't seem to figure out how to change the IDs in the middle of an insert select statement.

Thank you

 

[skuhlman]

insert into PropTable (id, Prop1, Prop2, Prop3...)
    select '789-456' as id, Prop1, Prop2, Prop3...
        from PropTable
        where id = '123-456'
update PropTable
    set id = '123-789'
    where id = '123-456'