I'm just getting into using SQL Server, so I'm a bit Transact-SQL challenged, shall we say. I am trying to move this query from Access into a stored procedure. I keep getting an error message that there is a syntax problem near the word INNER.

Any ideas on how I have to word this to get it to work?


UPDATE [history - 6165 shipment] INNER JOIN [base - 6165 shipment] ON
[history - 6165 shipment].LAST_GID = [base - 6165 shipment].LAST_GID SET
[base - 6165 shipment].MATERIAL = [history - 6165 shipment].material,
[base - 6165 shipment].BAS_SHP_QTY = [history - 6165 shipment].BAS_SHP_QTY,
[base - 6165 shipment].BAS_CNFRMD_QTY = [history - 6165 shipment].BAS_CNFRMD_QTY,
[base - 6165 shipment].SIZE_CD = [history - 6165 shipment].SIZE_CD,
[base - 6165 shipment].PLANT = [history - 6165 shipment].PLANT,
[base - 6165 shipment].DIVISION = [history - 6165 shipment].DIVISION,
[base - 6165 shipment].SLS_ORG = [history - 6165 shipment].SLS_ORG,
[base - 6165 shipment].REGION = [history - 6165 shipment].REGION,
[base - 6165 shipment].ORDER_TYPE = [history - 6165 shipment].ORDER_TYPE,
[base - 6165 shipment].ITEM_CAT = [history - 6165 shipment].ITEM_CAT,
[base - 6165 shipment].extract_dt = [history - 6165 shipment].extract_dt;

Howsit

Sorry, I have been staring at a screen all day today, and I have been seeing things. I replaced your table names with Aliases for the update and set... Feel free to change back.

I am getting more and more into the SQL side, but I am by no means an expert. Try...

UPDATE
t2
SET
t1.MATERIAL = t2.material,
t1.BAS_SHP_QTY = t2.BAS_SHP_QTY,
t1.BAS_CNFRMD_QTY = t2.BAS_CNFRMD_QTY,
t1.SIZE_CD = t2.SIZE_CD,
t1.PLANT = t2.PLANT,
t1.DIVISION = t2.DIVISION,
t1.SLS_ORG = t2.SLS_ORG,
t1.REGION = t2.REGION,
t1.ORDER_TYPE = t2.ORDER_TYPE,
t1.ITEM_CAT = t2.ITEM_CAT,
t1.extract_dt = t2.extract_dt
from
[base - 6165 shipment] t1
inner join
[history - 6165 shipment] t2 on
t1.LAST_GID = t2.LAST_GID

Thanks Kiwiman! Worked like a charm. I'm adding to your rep (and when I add to your rep, it is a big jump due to my time on the board and the number of posts :D )

sweet - only been a member a few days... Congtrats on your 10k posts - legend.