Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Zath
Constraint Violating Yak Guru
298 Posts |
Posted - 2012-11-19 : 14:44:05
|
Ran into a wall here and need some input.--@my_Table_ids is a user defined table type that is a list of PK's--This INSERT works just fineINSERT INTO dbo.MyMainTable (MyMainField)SELECTMyMainFieldFROM dbo.MyMainTable_TEMP WHERE MY_PK IN (SELECT id FROM @my_Table_ids) --Now, I need the new list of PK's that were created --above and update another table that which--also contains the list @my_Table_ids--NOT SURE ABOUT THIS UPDATE for the WHERE clauseUPDATE MyXTable SET MY_MyMainTable_FK = (SELECT MY_PK FROM dbo.MyMainTable WHERE) WHERE MyTempFK IN (SELECT id FROM @my_Table_ids) The Update is what I think may work but need a second opinion.Thanks! |
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2012-11-19 : 15:22:47
|
are you looking for something like this?UPDATE MyXTableSET MyXTable.Field1 = MyMainTable. Field1FROMMyXTable INNER JOIN MyMainTableON MyXTable.Field2 = MyMainTable. Field2WHERE ... |
|
|
Zath
Constraint Violating Yak Guru
298 Posts |
Posted - 2012-11-19 : 15:38:48
|
I really don't think that would do what I need.I've been looking into OUTPUT and came up with this:DECLARE @MyTableVar TABLE( id BIGINT);INSERT INTO dbo.MyMainTable (MyMainField)OUTPUT INSERTED.MY_PK INTO @MyTableVarSELECTMyMainFieldFROM dbo.MyMainTable_TEMP WHERE MY_PK IN (SELECT id FROM @my_Table_ids) --BELOW WON"T WORK???????????UPDATE MyXTable SET MY_MyMainTable_FK = (SELECT MY_PK FROM dbo.MyMainTable IN (SELECT id FROM @MyTableVar))WHERE MyTempFK IN (SELECT id FROM @my_Table_ids) I have a list of the new PK's and the original list of the other table's PK's, both in the perfect order that is needed.Now to match them....Suggestions? |
|
|
Zath
Constraint Violating Yak Guru
298 Posts |
Posted - 2012-11-19 : 15:52:56
|
Ok, I think I got it working. It should have been:UPDATE MyXTable SET MY_MyMainTable_FK = (SELECT MY_PK FROM dbo.MyMainTable WHERE MY_PK IN (SELECT id FROM @MyTableVar))WHERE MyTempFK IN (SELECT id FROM @my_Table_ids) |
|
|
Zath
Constraint Violating Yak Guru
298 Posts |
Posted - 2012-11-19 : 16:29:58
|
Unfortunately, this is NOT working..... |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2012-11-19 : 16:58:36
|
To save a lot of back and forth could you post DDL and DML with a few rows of sample data and expected results. Please make it runnable code so we don't have to type it all out.I'm assuming your primary key is an identity column and you are having trouble correlating the temp ids with the actual (different) ones generated by the insert? but you didn't say that so I'm not sure. Another "red flag" I heard is "both in the perfect order that is needed". Order in the table is irrelevant. You would use an ORDER BY clause in a SELECT statement to present the rows in whatever order you want. So not sure what you mean there.But the DDL,DML, and expected results should answer all that.Be One with the OptimizerTG |
|
|
|
|
|