Update Query 1

[Valeriya]

Hey, Guys!
Trying to accomplish fairly simple task; however, for some reason it's not working.
I have two tables [000000 Cons Request History] and [dbo_ItmMstr].

1.Every InvMajCls field from [000000 Cons Request History]Table needs to be updated with a corresponded MajorCls from[dbo_ItmMstr]Table and those two tables should be linked by Item field.

Here what I've got so far:

UPDATE [000000 Cons Request History] LEFT JOIN dbo_ItmMstr ON [000000 Cons Request History].Item = dbo_ItmMstr.Item SET [000000 Cons Request History].InvMajCls = "InvMajCls"
WHERE ((([000000 Cons Request History].InvMajCls)<>[dbo_ItmMstr].[MajorCls]));

the result is weird, it populates the column with "inv" instead of numbers
InvMajCls
Inv
Inv
Inv

Thank you in advance for your advise!
Valeriya

 

[PHV]

Perhaps this ?
UPDATE [000000 Cons Request History] INNER JOIN dbo_ItmMstr ON [000000 Cons Request History].Item = dbo_ItmMstr.Item
SET [000000 Cons Request History].InvMajCls = [dbo_ItmMstr].[MajorCls]
WHERE [000000 Cons Request History].InvMajCls <> dbo_ItmMstr.MajorCls;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Valeriya]

Thank you, PHV, but it still doing same thing.

1.[000000 Cons Request History]Table has a wrong info
and needs to be updated.
2. So InvMajCls from [00000 Cons Request History]Table
should be replaced with new value of MajorCls from dbo_itmMstr

Please advise,

Thanks a lot again,

Valeriya

 

[Valeriya]

sorry, PHV
I didn't mean to break rules,I though maybe I wasn't clear... The Inner Join Change is giving me same results...

Thanks a lot,

Valeriya

 

[PHV]

WHICH results ?
could you please post some input samples, actual result and expected result ?

BTW, are you sure you EXECUTED the query (the ! button) instead of only PREVIEW the candidate records (datasheet view pane) ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Valeriya]

Dear, PHV. I'm truly thankfull to you for all your help!Don't know what I would do without it. You were excatly right, I had to actually run the query, instead of looking at the preview results. Now it works just fine.

Thanks again for you kind help!

Valeriya