Update Query (Resent)

  • Thread starter Thread starter Steve Andler
  • Start date Start date
S

Steve Andler

Hi ;

Is it possible to update multiple colums with one query .
The reason i need that is there are 15 colums to be
updated in a table which means 15 seperate queries . I
have written down my query as it can only update one
column for the moment .

P.S:Update query refers to a reference table for the
colums to be updated.


UPDATE [ExistingTable] INNER JOIN ConversionTable ON
[ExistingTable].[Code 1]=[ConversionTable].[Old Code] SET
[ExistingTable].[Code 1] = [ConversionTable].[NewCode];

Thanks for any help.
 
Try...

UPDATE
[ExistingTable]
SET
[ExistingTable].[Code 1] = [ConversionTable].[NewCode],
[ExistingTable].[Code 2] = [ConversionTable].[NewCode2],
[ExistingTable].[Code 3] = [ConversionTable].[NewCode3],
[ExistingTable].[Code 4] = [ConversionTable].[NewCode4]
FROM
[ExistingTable]
INNER JOIN ConversionTable ON
[ExistingTable].[Code 1]=[ConversionTable].[Old Code]
WHERE
...
 
You MIGHT be able to do this with multiple aliases of your conversion table. But...

UPDATE (((ExistingTable as E Left Join ConversionTable as C1
ON E.Code1 = C1.OldCode)
Left Join ConversionTable as C2
ON E.Code2 = C2.OldCode)
Left Join ConversionTable as C3
ON E.Code3 = C3.OldCode)
Left Join ConversionTable as C4
ON E.Code4 = C4.OldCode
Set E.Code1 = C1.NewCode,
E.Code2 = C2.NewCode,
E.Code3 = C3.NewCode,
E.Code4 = C4.NewCode

This is UNTESTED code and I hesitated to suggest it, but it may work. Please
let me know if it does. Hopefully you can extend the logic for all fifteen
fields. You will end up with fourteen "(" after the word "Update" and then one
after each On clause with the exception of the last one.
 
Hi Chris .

Thanks for your reply .
However i still have some questions
I do not understand what i am suppose to write after "WHERE" statement . Could you be more specific
And I do not understand why [New Code] is changed to NewCode1,2,3...in the string since [New Code] is onl
one column inthe table (ConversionTable) adn has all the new codes in .

Thanks in advanc
Stev

----- Chris wrote: ----

Try..

UPDAT
[ExistingTable]
SET
[ExistingTable].[Code 1] = [ConversionTable].[NewCode]
[ExistingTable].[Code 2] = [ConversionTable].[NewCode2]
[ExistingTable].[Code 3] = [ConversionTable].[NewCode3]
[ExistingTable].[Code 4] = [ConversionTable].[NewCode4
FRO
[ExistingTable]
INNER JOIN ConversionTable ON
[ExistingTable].[Code 1]=[ConversionTable].[Old Code]
WHER
..
-----Original Message---- Hi ;
Is it possible to update multiple colums with one query .
Click to expand...
The reason i need that is there are 15 colums to be
updated in a table which means 15 seperate queries . I
have written down my query as it can only update one
column for the moment .
P.S:Update query refers to a reference table for the colums to be updated
UPDATE [ExistingTable] INNER JOIN ConversionTable ON
Click to expand...
Click to expand...
[ExistingTable].[Code 1]=[ConversionTable].[Old Code] SET
[ExistingTable].[Code 1] = [ConversionTable].[NewCode]
Thanks for any help.
Click to expand...
Click to expand...
 
Back
Top