Update last record inserted from another table

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Update last record inserted from another table

Author

Topic

ConfusedAgain
Yak Posting Veteran

82 Posts

Posted - 2012-01-31 : 12:15:22

I want to update the last inserted record in a table with the address details from another table.

I have two tables

DeliveryAddress
DeliveryAddress.AccID
DeliveryAddress.Address

AccountDetails
AccountDetails.AccID
AccountDetails.Address

When a record is inserted into the DeliveryAddress table the DeliveryAddress.AccID is populated but the Address is not. I need to pick this up from the AccountDetails table. Using After Insert trigger how can I do this?

Hope that makes sense let me know if not.
Thanks.

ConfusedAgain
Yak Posting Veteran

82 Posts

Posted - 2012-01-31 : 12:18:48

I should add there is a DeliveryAddress.ID that is an incremental Autonumber. as there may be muliple records of the DeliveryAddress.AccID and I only want to update the last record inserted.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-01-31 : 12:24:44

Can you show us the code that does the actual insert? You could probably do it from there and bypass the trigger entirely

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

ConfusedAgain
Yak Posting Veteran

82 Posts

Posted - 2012-01-31 : 12:27:39

The code that does the insert is in a vb.net form and that has no reference to the other table. It is an insert statement:

mySqlCommand.CommandText = "INSERT INTO DeliveryAddress (AccID, AccountName, , UserName) " _
& "VALUES ( @pAccID, @pAccountName, @pUserName)"

ConfusedAgain
Yak Posting Veteran

82 Posts

Posted - 2012-01-31 : 15:19:53

Can anyone help on this one I am a bit stuck?

Thanks

ConfusedAgain
Yak Posting Veteran

82 Posts

Posted - 2012-01-31 : 15:43:52

AFTER INSERT
AS
Update DeliveryAddress
SET DeliveryAddress.DelAdd1 = AccountDetails.Address1
FROM AccountDetails
WHERE DeliveryAddress.AccID = AccountDetails.CompanyID

No problem - I had a mental block. I have resolved this with the above.
Thank you for looking.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources