Update a table using data 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

General SQL Server Forums

New to SQL Server Programming

Update a table using data from another table

Author

Topic

andy1981
Starting Member

2 Posts

Posted - 2012-06-30 : 18:50:09

I have 2 tables:
tblCosts:
- Id
- Date
- TrajectId
- Reason

tblTraject:
- TrajectID
- Distance
- TrajectInformation

Both are linked via TrajectID.
I want to update tblCosts.TrajectId with another TrajectID dependent on the information tblTraject.TrajectInformation i have and the row from tblCosts via tblCosts.Id.

Is there is a possibility to do this in one SQL statement? I tried a lot of things but I do not have much knowledge of difficult SQL statements.

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2012-06-30 : 20:47:55

quote:
Originally posted by andy1981

Both are linked via TrajectID.
I want to update tblCosts.TrajectId with another TrajectID dependent on the information tblTraject.TrajectInformation i have and the row from tblCosts via tblCosts.Id.

Do you want to update or insert? For example, do you want to change the information you have in the tblCosts table or do you want to add a new record to the tblCosts table?

quote:

Is there is a possibility to do this in one SQL statement? I tried a lot of things but I do not have much knowledge of difficult SQL statements.

It would be very helpful if you posted some sample data, a query that you've written or something to give us something to go on.

sshelper
Posting Yak Master

216 Posts

Posted - 2012-06-30 : 21:19:42

To update one table with data from another table, the basic structure is as follows

UPDATE A
SET TrajectId = <New Value Here>
FROM tblCosts A INNER JOIN tblTraject B ON A.TrajectID = B.TrajectID

SQL Server Helper
http://www.sql-server-helper.com/error-messages/msg-1-500.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-01 : 00:49:10

quote:
Originally posted by andy1981

I have 2 tables:
tblCosts:
- Id
- Date
- TrajectId
- Reason

tblTraject:
- TrajectID
- Distance
- TrajectInformation

Both are linked via TrajectID.
I want to update tblCosts.TrajectId with another TrajectID dependent on the information tblTraject.TrajectInformation i have and the row from tblCosts via tblCosts.Id.

Is there is a possibility to do this in one SQL statement? I tried a lot of things but I do not have much knowledge of difficult SQL statements.

sorry not very clear against what field you want to compare value of TrajectInformation for doing update of TrajectID. can you elaborate?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

andy1981
Starting Member

2 Posts

Posted - 2012-07-01 : 10:10:41

For example:
I want to update in tblCosts the traject with Id=7.
But i have only the TrajectInformation 'TrajectHomeToWork' and not the new TrajectId.

The SQL from sshelper should only help if i had the correct TrajectId from the table tblTraject.

I thought of the following:

UPDATE A
SET TrajectId = (select TrajectId from B WHERE B.TrajectInformation='TrajectHomeToWork')
FROM tblCosts A INNER JOIN tblTraject B ON A.TrajectID = B.TrajectID
WHERE A.Id=7

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources