Help with update query

[PreacherUK]

Hey all,

I'm sure that I'm just missing something very obvious here but as with these kinds of things I can't see it.

Code:
UPDATE dbo.[tblGlobal_Availability] LEFT OUTER JOIN
dbo.[tblGSP55S9X - Security Data] ON dbo.tblGlobal_Availability.Isin =dbo.[tblGSP55S9X - Security Data].ISIN
SET dbo.tblGlobal_Availability.[Dirty Price] =dbo.[tblGSP55s9x - security data].[price]
WHERE ((dbo.tblGlobal_Availability.[Dirty Price])=0)


In short what I'm trying to do is update the first table tblGlobal_Availability, with a value 'price' from the second table 'tblGSP55s9x - security data'

I keep getting the error message: Incorrect syntax near the keyword 'LEFT'.

Can anyone help?

 

[SmileyConspiracy]

This should work.

UPDATE dbo.[tblGlobal_Availability]
SET dbo.[tblGlobal_Availability].[Dirty Price] =SecurityData.[price]
FROM dbo.[tblGlobal_Availability] Availability
LEFT OUTER JOIN dbo.[tblGSP55S9X - Security Data] SecurityData
ON Availability.Isin =SecurityData.ISIN
WHERE Availability.[Dirty Price] =0

However using an outer join will mean that all of the rows in tblGlobal_Availability with a Dirty Price of 0 that have no matching row in tblGSP55S9X - Security Data will have their Dirty Price set to null. If you don't want this to happen change the query to use an inner join so that only matching rows are updated.

 

[PreacherUK]

Cheers,
this did the trick. Was it just down to my placing of the join command and things? (sorry for being non-specif )

I'm much more used to M$Access and as such i was still in an Access mind set.

But again, thanks for your help.

 

[PreacherUK]

Ah, I spoke too soon. This has appeared to update the 'dirty price' field with just one price, so now every entry has the same price...which is a bit wrong

 

[SmileyConspiracy]

Strange, can I see a sample of your data, just the fields
tblGlobal_Availability: Isin, Dirty Price
and tblGSP55S9X - Security Data: ISIN, price

 

[PreacherUK]

It's cool furbank, I was being dim. I took your advice too and made it an INNER join, it all works great.