Link to home
Start Free TrialLog in
Avatar of zimmer9
zimmer9Flag for United States of America

asked on

How to update fields in 1 table based upon matching values in another table ?

I am developing an Access Application using Access as the front end database and SQL Server as the Back end database.

I have a table named tblA with fields FldA, FldB, FldC and FldD.
I have another table named tblB with field FldA, FldB, FldC and FldD.

When tblA.FldA = tblB.FldA, I want to populate
tblB.fldC with the value in tblA.fldC and
populate tblB.fldD with the value in tblA.fldD

Do you know how I could accmplish this SQL Update ?
Avatar of Raynard7
Raynard7

update tblA, tblB set tblB.fldC = tblA.fldC and tb.B.fldD = tblA.fldD where tblA.FldA = tblB.fldB

would do it for you.
sorry that should be

update tblA, tblB set tblB.fldC = tblA.fldC, tb.B.fldD = tblA.fldD where tblA.FldA = tblB.fldB
Avatar of zimmer9

ASKER

I actually used different variable names but the concept is the same as follows:

update dbo.tblKC1, dbo.tblKC2
set dbo.tblKC2.Application = dbo.tblKC1.Application,
dbo.tblKC2.[Rollup Application] = dbo.tblKC1.[Application Group]
where dbo.tblKC1.GRN = dbo.tblKC2.GRN

Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near ','.

Do you know the cause of the syntax error that is being generated ?
I am using SQL Query Analyzer.
alternatively you could try

update dbo.tblKC1 inner join dbo.tblKC2 on dbo.tblKC1.GRN = dbo.tblKC2.GRN
set dbo.tblKC2.Application = dbo.tblKC1.Application,
dbo.tblKC2.[Rollup Application] = dbo.tblKC1.[Application Group]

for more examples see

http://www.devguru.com/Technologies/sqlsyntax/quickref/sql_syntax_update.html
Avatar of zimmer9

ASKER

So you can use the update command on tblKC1 even though this table is not being updated but merely serves as the lookup table to populate values into tblKC2.
Avatar of zimmer9

ASKER

update tblKC1 inner join tblKC2 on tblKC1.GRN = tblKC2.GRN
set tblKC2.Application = tblKC1.Application,
tblKC2.[Rollup Application] = tblKC1.[Application Group]

Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'inner'.
ASKER CERTIFIED SOLUTION
Avatar of Raynard7
Raynard7

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of zimmer9

ASKER

update tblKC2
set tblKC2.Application = tk1.Application,
tblKC2.[Rollup Application] = tk1.[Application Group]
FROM tblKC2 as tk2 inner join tblKC1 as tk1 on tk1.GRN = tk2.GRN

this works