CODEWITHSUNDEEP
sql-server
FirmanHidayat
FirmanHidayat

Reputation: 347

How to update for each records without looping in SQL Server

I have a table and contained data show below, it's called TABLE_A

+++++++++++++++++++++++++++++++++++++++++++++
PrimaryID | Col2           | SecondaryID
+++++++++++++++++++++++++++++++++++++++++++++
1         | Description 1  | 0
2         | Description 2  | 0
3         | Description 3  | 0
4         | Description 4  | 0
.         | ...            | .
.         |
.

please see SecondaryID, above. its has zero value as an initial value

and I have another table, it's called TABLE_B, below

+++++++++++++++++++++++++++++++++++++++++++++
PrimaryID | Col2           | ForeignKeyID
+++++++++++++++++++++++++++++++++++++++++++++
1         | Description 1  | 123
2         | Description 2  | 320
3         | Description 3  | 111
4         | Description 4  | 999
.         | ...            | .
.         |
.

I have trouble in SQL Server, How to update SecondaryID column on TABLE_A with ForeignKeyID value on TABLE_B for each row in TABLE_A's PrimaryID is equal TABLES_B's PrimaryID. But, I don't want to solve this problem using LOOPING CURSORS or another else.

Are there a simple way??

I need urgent, and thank you in advanced.

Upvotes: 1

Views: 8336

Answers (1)

davesnitty
davesnitty

Reputation: 1850

Yes, you can do a join within an UPDATE operation. As follows:

UPDATE table_a SET SecondaryID = b.ForeignKeyID
FROM table_a a
    JOIN table_b b
    on a.PrimaryID = b.PrimaryID

http://sqlfiddle.com/#!3/fdcdb/6

Upvotes: 10

Related Questions