Update query

[Rupa]

Hey all

I am trying to update a column in a table with this:

SELECT IIf(Len([temp].[Magstripe])>10,Right([temp].[Magstripe],9),Right([temp].[Magstripe],6)) AS Expr1
FROM temp

How would I do this?

I tried:

UPDATE temp SET temp.Magstripe = (SELECT IIf(Len([temp].[Magstripe])>10,Right([temp].[Magstripe],9),Right([temp].[Magstripe],6)) AS Expr1
FROM temp)

but I get the following error:

"Operation must use an updateable query"

Any suggestions would be highly appreciated.

Many thanks,

Rupa

 

[LPurvis]

Drop the SELECT keyword. It's unnecessarily confusing the parser.
Your expression refers only to columns within your updated table. The expression alone is valid.

 

[Rupa]

Thanks for the reply. I've now got:

UPDATE temp SET temp.Magstripe = (IIf(Len([temp].[Magstripe])>10,Right([temp].[Magstripe],9),Right([temp].[Magstripe],6)) AS Expr1
FROM temp)

And it gives me the following error:

Syntax error (missing operator) in query expression
'(IIf(Len([temp].[Magstripe])>10,Right([temp].[Magstripe],9),Right([temp].[Magstripe],6)) AS Expr1
FROM temp)'.

And it's flashing on AS

Any suggestions?

Many thanks,

Rupa

 

[LPurvis]

Indeed - there's no need to alias your expression.
It's just a return value into an Update column assignment.
(i.e. drop "AS Expr1")

 

[Rupa]

Thank you LPurvis. I didn't need the FROM temp either so this worked:

UPDATE temp SET temp.Magstripe = (IIf(Len([temp].[Magstripe])>10,Right([temp].[Magstripe],9),Right([temp].[Magstripe],6)))

Many, many thanks,

Rupa

 

[LPurvis]

There was a FROM clause in there? lol
Sorry - shows how much attention I'm paying this morning. Too busy!

 

[Rupa]

LOL..Your suggested solutions worked so it looks like you are paying attention

Thanks again,

Rupa