Menu
In SQL, say I have a table of the following layout:
id name age
1 george 12
2 tom 14
3 charles 19
4 henry 21
5 fiona 12
6 kate 14
...
If I discover that I've made a terrible inputting mistake, and that everybody is actually twice their age, is there a way to multiply the age column by 2 in one sweep instead of needing to painstakingly go through the whole column and edit each age individually (pretend I have 500 entries so manual work is out of the question).
Is there a SQL solution?
404
We are interested in x = a*b . Then, applying some math, we have: x = a * b -> log(x) = log(a * b) -> log(x) = log(a) + log(b) -> exp[log(x)] = exp[log(a) + log(b)] -> x = exp[log(a) + log(b)].
The SQL multiply ( * ) operator is used to multiply two or more expressions or numbers.
There is no PRODUCT set function in the SQL Standard. It would appear to be a worthy candidate, though (unlike, say, a CONCATENATE set function: it's not a good fit for SQL e.g. the resulting data type would involve multivalues and pose a problem as regards first normal form).
To multiply more than two columns in Excel, you can use the multiplication formulas similar to the ones discussed above, but include several cells or ranges. For example, to multiply values in columns B, C and D, use one of the following formulas: Multiplication operator: =A2*B2*C2. PRODUCT function: =PRODUCT(A2:C2)
It's really easy:
UPDATE table SET age=age*2
Yes, run an update:
update theTable set age = age * 2
Depending on the settings in the database, you might not be allowed to run an update without a where (to protect against mistakes), then you would add a dummy comparison:
update theTable set age = age * 2 where 1 = 1
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
