UPDATE table2 with value from table1 on INSERT into table1

Question

UPDATE table2 with value from table1 on INSERT into table1

kabaari

SSCrazy

Points: 2235
More actions
August 26, 2009 at 6:39 am

#213751

Good morning all,
And thank i advance. I need to "UPDATE table2.ID with value from table1.ID on INSERT into table1"
Table1.ID is Identity Insert but table2.ID is Nullable. I want when I insert values into table1 that table2 get the same ID from table for future queries.
UPDATE tbl_Assembly_Delay1.ID
SET tbl_Assembly_Production.ID = tbl_Assembly_Delay1.ID
FROM tbl_Assembly_Production
WHERE (tbl_Assembly_Production.ID = tbl_Assembly_Delay1.ID)
Thanks again,
DJ Khalif

Viewing 7 posts - 1 through 6 (of 6 total)

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

matt6288 SSCertifiable Points: 5058 More actions · Answer 1

It sounds like you need an insert trigger on table 1. I can't rattle off the syntax for this off the top of my head but you can look up "triggers" in books online and see if that will do what you are needing.

SQLZ SSChampion Points: 12872 More actions · Answer 2

kabaari (8/26/2009)
Good morning all,
And thank i advance. I need to "UPDATE table2.ID with value from table1.ID on INSERT into table1"
Table1.ID is Identity Insert but table2.ID is Nullable. I want when I insert values into table1 that table2 get the same ID from table for future queries.
UPDATE tbl_Assembly_Delay1.ID
SET tbl_Assembly_Production.ID = tbl_Assembly_Delay1.ID
FROM tbl_Assembly_Production
WHERE (tbl_Assembly_Production.ID = tbl_Assembly_Delay1.ID)
Thanks again,
DJ Khalif

Something doesn't quite seem right here.....

If the ID value in table2 is nullable how will you ever be able to link up table2 with table1?

Let's follow this through the logical sequence....

1. You insert a record into table1.

2. You can use scope_identity() to get the identity value of the record you just inserted.

3. So now you want to update table2's ID column with this ID you just inserted. But which row are you going to update? In other words, what conditions are you using to determine which row you want to update in table2?

Hope that makes sense.

Karl
source control for SQL Server

kabaari SSCrazy Points: 2235 More actions · Answer 3

Thanks in advance:

I realized that logically I was incorrect. Below is where I attempted to correct my mistake. Now I get: "Incorrect syntax near 'Code'".

INSERT into tbl_Assembly_Delay(ID, Code, DelayTime)

( '#Trim(FORM.Code)#',

'#Trim(FORM.DelayTime)#', SELECT ID

FROM tbl_Assembly_Production

WHERE tbl_Assembly_Production.WorkOrder = '#FORM.WorkOrder#');

kabaari SSCrazy Points: 2235 More actions · Answer 4

I made another change and I get this: Incorrect syntax near ','.

INSERT into tbl_Assembly_Delay(ID, Code, DelayTime)

SELECT ID

FROM tbl_Assembly_Production

WHERE tbl_Assembly_Production.WorkOrder = '#FORM.WorkOrder#' AND ('#Trim(FORM.Code)#' ,

'#Trim(FORM.DelayTime)#');

SQLZ SSChampion Points: 12872 More actions · Answer 5

Try this:

INSERT into tbl_Assembly_Delay(ID, Code, DelayTime)

SELECT ID, '#Trim(FORM.Code)#', '#Trim(FORM.DelayTime)#'

FROM tbl_Assembly_Production

WHERE tbl_Assembly_Production.WorkOrder = '#FORM.WorkOrder#' ;

Karl
source control for SQL Server

kabaari SSCrazy Points: 2235 More actions · Answer 6

Thanks. That was the same thing I came up with:

INSERT INTO tbl_Assembly_Delay1(ID, Code, DelayTime)

SELECT ID , '#Trim(FORM.Code)#', '#Trim(FORM.DelayTime)#'

FROM tbl_Assembly_Production

WHERE tbl_Assembly_Production.WorkOrder = '#FORM.WorkOrder#'

I also ran into some other issues but figured those too. Thanks again.