chris_lee100
Member
Registered: 14th Jul 02
User status: Offline
|
I have a number of records with a field in which has a number I wish to add eg I want to make the record 100 into 1100. What is the sql in the "update to" field for this to happen?
Cheers, Chris
|
Joff
Member
Registered: 17th Oct 00
Location: Cambridgeshire
User status: Offline
|
code:
UPDATE table_name SET field_name = new_value WHERE field_name = old_value
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Online
|
Don't think you can do this with a single UPDATE.
Also consider whether this is a bit of maths in which you're adding 1000, or a string operation in which you're placing a 1 at the front of the existing number. Its not the same operation.
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Online
|
You can't say this though, unless Access suports sub-selects?
code:
UPDATE table_name SET field_name = "1" & old_value WHERE field_name = old_value
[Edited on 30-01-2005 by Ian]
|
Joff
Member
Registered: 17th Oct 00
Location: Cambridgeshire
User status: Offline
|
quote:
Originally posted by Ian
Don't think you can do this with a single UPDATE.
Also consider whether this is a bit of maths in which you're adding 1000, or a string operation in which you're placing a 1 at the front of the existing number. Its not the same operation.
From the sounds of it, he's just updating the value, not performing a sum.
Multiple updates will work with that code above in Access, SQL 2k and mySQL. Just tried.
|
Joff
Member
Registered: 17th Oct 00
Location: Cambridgeshire
User status: Offline
|
quote:
Originally posted by Ian
You can't say this though, unless Access suports sub-selects?
code:
UPDATE table_name SET field_name = "1" & old_value WHERE field_name = old_value
Why would you want to do that tho?
code:
UPDATE table SET field=1100 WHERE field=100
Works fine for the given brief, assuming field datatype is numeric.
|
