CODEWITHSUNDEEP
sql-serversql-server-2005ms-access
user1366051
user1366051

Reputation: 11

UPDATE one table based on values in another table

I have two tables, Table 1 has three fields (a1,a2,a3) and table 2 has three fields (b1,b2,b3) I want to update table one base on table two when a1=b1 . This is essay in Microsoft access but I want t to do it in Microsoft Access project Sql server 2005 as a back end.

UPDATE  t1
SET  a1 = t2.b1, a2 = t2.b2, a3 = t2.b3
where a1=b1

Upvotes: 1

Views: 1198

Answers (2)

Wagner DosAnjos
Wagner DosAnjos

Reputation: 6374

Try the following for SQL Server:

UPDATE t1 
   SET a1 = t2.b1, a2 = t2.b2, a3 = t2.b3
FROM t1
   INNER JOIN t2 ON t1.a1 = t2.b1

Upvotes: 0

HansUp
HansUp

Reputation: 97101

INNER JOIN the tables. Then you won't need to SET t1.a1=t2.b1 because those are already equal based on the join condition. So just SET the other two field values ...

UPDATE t1 INNER JOIN t2
ON t1.a1=t2.b1
SET t1.a2 = t2.b2, t1.a3 = t2.b3

Note this suggestion is Access SQL and should work from MS Access regardless of whether t1 and t2 are native Access tables, links to SQL Server tables, or a mix of both types.

Upvotes: 2

Related Questions