Update data from another table

Question

I need to update some data from another table..

I want to simply take the data from col1 in table1 to col1 in table2, the rows are at the same position so there aint really any id-comparing thats need to be done..

How can I do this?

----------------------------------------- Db1.Table1 (Take yearModel from this table to update Table3) ----------------------------------------- imgid | int | PK | yearModel | int | ----------------------------------------- ----------------------------------------- Db2.Table2 (Go by this table to update Table3) ----------------------------------------- imgid | int | PK | uploadId | int | FK (With table3.uploadId) | ------------------------------------------ ------------------------------------------ Db2.Table3 (Update this table with the yearModel from table1) ------------------------------------------- uploadId | int | PK | uploadYearModel | int |

Sorry, my database diagaram "thingy" has crashed for some wierd reason.. :( I hope you ge the idea

I should also mention that Table1 is located in another database..which Im not sure if that matters.. but well.. :)

Thanks in advance!

and there are like 6000 rows in each table.. and I need col1 on row1 in table2 to be updated with col1 in row1 in table1 and then col1 on row2 in table2 to be updated with col1 in row2 in table1 and so on.. — Inx
– Inx, Commented Jul 10, 2012 at 9:08
Can you please show us some table structures etc. and explain a bit more what you're trying to do ?? And what do you mean by same position?? SQL Server doesn't have "implicit" positions or row numbers or anything - if you want to compare rows from two tables, you must have some kind of common ID or something to go on .... — marc_s
– marc_s, Commented Jul 10, 2012 at 9:09

Yaqub Ahmad · Accepted Answer · 2012-07-10 09:26:31Z

If you have a Common Key Column in both tables & this key column is having the same values in both tables (they refernce the same rows in both tables ) then below query should work:

UPDATE Table1 SET Column1 = t2.Column1 FROM Table2 t2 INNER JOIN Table1 t1 ON t2.KeyColumn = t1.KeyColumn

EDIT:

UPDATE Table3 SET uploadYearModel = t1.yearModel FROM Table1 t1 INNER JOIN Table2 t2 ON t2.imgid = t1.imgid INNER JOIN Table3 t3 ON t3.uploadId = t2.uploadId

Germann Arlington · Accepted Answer · 2012-07-10 09:26:32Z

To update existing records:

UPDATE table2 SET table2.c1 = table1.c1, table2.c2 = table1.c2, table2.c3 = table1.c3 FROM table1, table2 WHERE table1.c1 = table2.c1

Nikson Kanti Paul · Accepted Answer · 2012-07-10 09:29:20Z

0

UPDATE table2 SET col1=( SELECT top 1 col1 FROM table1 WHERE table1.id=table2.id )

edited Jul 10, 2012 at 9:29

Nikson Kanti Paul

3,4381 gold badge37 silver badges51 bronze badges

answered Jul 10, 2012 at 9:11

Vikram Jain

5,6081 gold badge22 silver badges31 bronze badges

Collectives™ on Stack Overflow

Update data from another table

3 Answers 3

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Linked

Related