Update from one table to another

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 2000 Forums

SQL Server Development (2000)

Update from one table to another

Author

Topic

misterzr
Starting Member

49 Posts

Posted - 2005-05-25 : 13:48:38

I have two tables, PRODDAT.F4101 and PRODDAT.F4102

I need to update PRODDTA.F4102 column IBAITM to PRODDTA.F4101 column IMATIM

SreenivasBora
Posting Yak Master

164 Posts

Posted - 2005-05-25 : 15:30:52

Update PRODDTA.F4102 as A
Set A.IBAITM = F4102.IMATIM
from A INNER JOIN F4101 ON A.COMMONCOLUMNID = F4101.COMMONCOLUMNID

With Regards
Sreenivas Reddy B

DWalker
Starting Member

1 Post

Posted - 2005-06-15 : 12:05:54

I wish the Help for "Update" in SQL Books online would show at least a single example of this. The examples for Update always show something trivial like increasing a price in one table by 10%.

Personally, I think it's silly to alias everything all the time, but 99% of the examples of ANYTHING done in SQL make the statement harder to read by using a table alias wherever it's possible to do so. That's just my personal opinion.

I think this is easier to read... as long as you know what's in tables with names like F4101 and F4102!

Update PRODDTA.F4102
Set IBAITM = PRODDTA.F4101.IMATIM
From PRODDTA.F4102 Inner Join PRODDTA.F4101
On PRODDTA.F4102.COMMONCOLUMNID = PRODDTA.F4101.COMMONCOLUMNID

I think "Set A.IBATIM = F4102.IMATIM" should have read "Set A.IBATIM = F4101.IMATIM" in the previous answer. misterzr wanted to set the values in F4102 FROM the table F4101. I think. It wasn't exactly clear from the question, actually, which way the update should go.

Basically,

Update TargetTable
Set SomeField = SourceTable.SomeField
From TargetTable Inner Join SourceTable
On TargetTable.CommonField = SourceTable.CommonField

The two "SomeField" fields might have different field names in the different tables:

Update TargetTable
Set SomeField = SourceTable.SomeOtherField
From TargetTable Inner Join SourceTable
On TargetTable.CommonField = SourceTable.CommonField

Kristen
Test

22859 Posts

Posted - 2005-06-15 : 12:37:13

I usually use the alias "U" for the table I want to update - even for a simple update I put the table name in the FROM so that I'm consistent.


UPDATE U
SET MyColumn = 'XXX'
FROM MyTable U


UPDATE U
SET TableA_ColA = TableB_ColB
FROM MyTableA U
     JOIN MyTableB
         ON TableB_ColPK = TableA_ColPK

Kristen

naveenagg
Starting Member

2 Posts

Posted - 2010-08-20 : 12:28:31

I have got a very peculiar problem. I am actually using sql server management studio express and whenever i write a query to update one table from another it appends a cross join in the end which results in wrong updation.
Today i tried the same query on command line using sqlcmd and it executed perfectly.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-20 : 13:33:00

quote:
Originally posted by naveenagg

I have got a very peculiar problem. I am actually using sql server management studio express and whenever i write a query to update one table from another it appends a cross join in the end which results in wrong updation.
Today i tried the same query on command line using sqlcmd and it executed perfectly.

you mean cross join is added automatically by management studio? I've never seen that. Are you sure you're using standard SSMS?

By the way why did you reopen this old thread? you should have posted this as a new thread

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources