MySQL Database MySQL Connect MySQL Create DB MySQL Create Table MySQL Insert Data MySQL Get Last ID MySQL Insert Multiple MySQL Prepared MySQL Select Data MySQL Delete Data MySQL Update Data MySQL Limit Data PHP - XML. Update Data In a MySQL. MySQL UPDATE multiple columns To update values in the multiple columns, you need to specify the assignments in the SET clause. For example, the following statement updates both last name and email columns of employee number 1056.
Active8 years, 4 months ago
I have a table of postcodes and I want to update each postcode with its 3 nearest neighbours. Ie to fill in the blanks in this table:
I've figured out a SELECT query to find the nearest postcodes and here is one clumsy way the first row could be updated:
However this will result in 3 select queries being run for each row update. It would be more efficient if there was some way to do what is expressed by this pseudo code:
The 'select query' in the above looks like this:
Is there anyway for the rows returned from the select to be put into a form that they can be used to update multiple fields?Thanks.
spiderplant0
spiderplant0spiderplant0
1,7358 gold badges42 silver badges75 bronze badges
4 Answers
ThomasThomas
57.5k8 gold badges81 silver badges128 bronze badges
You can do something similar to this:
I found this related question on Stackoverflow on how to transform columns to rows:
In your case, you can do something like
Here is a hack to simulate the ROW_NUMBER() functionality in MySQL [1]:
Community♦
rkgrkg
4,0786 gold badges30 silver badges46 bronze badges
I think you could do this with the pseudo-code:
it'd be easier to specify it seeing the real SQL.
Note the first column is specified as a constant in quotes. For this to work
postcode
must be a UNIQUE
or PRIMARY
index.
James CJames C
Anytime I see a table with columns that have 1-up counters after their names, I get concerned.
In general, it is a Bad Idea (TM) to store data that can be calculated from data that is already stored. What happens if your application all of a sudden needs the 4 closest postal codes? What if the postal code boundaries change?
Assuming the distance calculation isn't very complex, you'll be better off in the long run not explicitly storing this data.
Chris MorganChris Morgan
Not the answer you're looking for? Browse other questions tagged mysql or ask your own question.
Active3 months ago
I need to update 2 datetime columns, and I need them to be exactly the same, using mysql version 4.1.20. I'm using this query:
It is safe or there is a chance that the columns are update with different time, because of the 2 visible calls to
I don't think that it can be update with different values (I think internally mysql calls
now()
?I don't think that it can be update with different values (I think internally mysql calls
now()
just once per row or something similar), but I'm not an expert, what do you think?
Update:Second question was extracted here.
Radu Maris
Radu Maris
Mysql Php Update Multiple Rows
Radu Maris
3,9633 gold badges33 silver badges52 bronze badges
7 Answers
Found a solution:
I found this in MySQL Docs and after a few tests it works:
the following statement sets col2 to the current (updated) col1 value, not the original col1 value. The result is that col1 and col2 have the same value. This behavior differs from standard SQL.
UPDATE t1 SET col1 = col1 + 1, col2 = col1;
![Mysql Mysql](/uploads/1/2/6/0/126076421/535247750.png)
![Mysql Php Update Multiple Columns In Mysql Mysql Php Update Multiple Columns In Mysql](/uploads/1/2/6/0/126076421/689638427.jpg)
3,9633 gold badges33 silver badges52 bronze badges
Mysql isn't very clever. When you want to use the same timestamp in multiple update or insert queries, you need to declare a variable.
When you use the
Cœur
now()
function, the system will call the current timestamp every time you call it in another query.
22.2k10 gold badges128 silver badges180 bronze badges
OlivierOlivier
You can store the value of a now() in a variable before running the update query and then use that variable to update both the fields
last_update
and last_monitor
.
This will ensure the now() is executed only once and same value is updated on both columns you need.
Sachin ShanbhagSachin Shanbhag
45.7k9 gold badges75 silver badges97 bronze badges
You can put the following code on the default value of the timestamp column:
blackpanther
CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
, so on update the two columns take the same value.
6,7217 gold badges39 silver badges69 bronze badges
DannyDanny
MySQL evaluates now() once per statement when the statement commences execution. So it is safe to have multiple visible now() calls per statement.
Rich AndrewsRich Andrews
If you really need to be sure that
now()
has the same value you can run two queries (that will answer to your second question too, in that case you are asking to update last_monitor = to last_update
but last_update
hasn't been updated yet)
you could do something like:
anyway I think that mysql is clever enough to ask for
blackpanther
now()
only once per query.
6,7217 gold badges39 silver badges69 bronze badges
sathiasathia
1,6192 gold badges17 silver badges37 bronze badges
Mysql Update Rows
There are 2 ways to this;
First, I would advice you declare now() as a variable before injecting it into the sql statement. Lets say;
Logically if you want a different input for last_monitor then you will add another variable like;
Mysql Update From Select
This way you can use the variables as many times as you can, not only in mysql statements but also in the server-side scripting-language(like PHP) you are using in your project.Remember these same variables can be inserted as inputs in a form on the front-end of the application. That makes the project dynamic and not static.
Secondly if now() indicates time of update then using mysql you can decalre the property of the row as a timestamp. Every time a row is inserted or updated time is updated too.
Wahinya BrianWahinya Brian