Prevent ON UPDATE CURRENT_TIMESTAMP
Does anybody know if there is a way to prevent an ON UPDATE CURRENT_TIMESTAMP from occuring if I don't want it to?
Only way I can see to do it is by altering the column properties to just a TIMESTAMP field, do the update, then alter the column back.
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Adding ON UPDATE CURRENT_TIMESTAMP To A Column
I'm trying to add ON UPDATE CURRENT_TIMESTAMP to a TIMESTAMP column but can't figure out the syntax. I tried various SQL strings including: ALTER TABLE `Content_elements` ALTER `last_updated` SET ON UPDATE CURRENT_TIMESTAMP
Default Current _Timestamp On Update Current_Timestamp
I'm getting an error, when I try to create a new table. CREATE TABLE `comments` ( `id_coment` int( 11 ) NOT NULL AUTO_INCREMENT , `n_person` varchar( 50 ) default NULL , `email` varchar( 100 ) default NULL , `comen` text, `place` varchar( 20 ) default NULL , `comment_date` timestamp NOT NULL default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP , PRIMARY KEY ( 'id_coment' ) )
Error #1293: There Can Be Only One TIMESTAMP Column With CURRENT_TIMESTAMP In DEFAULT Or ON UPDATE Clause
I am using MySQL 4.0/4.1 version. And I am trying to add two timestamp columns to a single table. The columns are insert_date and updat_date to capture the date/time the record was initially inserted as well as the date/time the record was last updated respectively. When I try to set one column (insert_date) with a default value of CURRENT_TIMESTAMP and the other column (updat_date) with ON UPDATE CURRENT_TIMESTAMP, I end up getting the following error: #1293 - Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause Is there any way around this problem?
CURRENT_TIMESTAMP
Here’s the table structure: Quote: CREATE TABLE `test` ( `test` varchar(20) character set utf8 NOT NULL default '', `timestamp` timestamp NOT NULL default '0000-00-00 00:00:00' on update CURRENT_TIMESTAMP ) When I insert a row, Quote: INSERT INTO test (test)VALUES ('test') timestamp doesn’t show current time, it shows the default '0000-00-00 00:00:00'. What am I doing wrong?
CURRENT_TIMESTAMP Limitations
I ran into what I consider to be a really whacky problem. Consider the following table: CREATE TABLE `mytable` ( `Id` INT(10) NOT NULL AUTO_INCREMENT, `Name` VARCHAR(256) CHARACTER SET utf8 NOT NULL, `DateCreated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `DateModified` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`Id`) ) ENGINE = INNODB; When I try to run this script, it gives me the following error: Error Code : 1293 Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause. Is there a way to work around this problem?
Incorrect Datetime Value ?? CURRENT_TIMESTAMP
I just wanted to add a TIMESTAMP column to store the date of each new record. The table is very simple (Id: INT, Type: INT, Value: VARCHAR(255)). So I just added another field "TypedDate: TIMESTAMP" DEFAULT: CURRENT_TIMESTAMP. And so MySQL won't update my table saying : "Warning: Incorrect datetime value: 'CURRENT_TIMESTAMP' for column 'EXP_TypedDate' at row 1". So the new column is added but the default value is "0000-00-00 00:00:00" instead of "CURRENT_TIMESTAMP".
Now() Or Current_timestamp() And The Hours Value In Time
When I run select now(); or select current_timestamp(), I see the output like "2005-04-19 02:50:14". By reading the documentation I thought the the hour value should be in military format namely 14, but instead I see 02. How can I fix this problem? I have MySQL 4.1.10a database running on RH 9.0 server.
Prevent Writes
How can I prevent writes to a mysql database but at the same time allow reads? This is in the case where I have a master/slave. The master got hosed so I promoted slave. Now I have the master back up and want to swap it back. So I need to stop writes to the slave, take a mysqldump, put it on the master and then start calling the master for writes.
Prevent MySQL Locking Up
I was doing some major work using MySQL this weekend, and I realized that MySQL is quite fragile. I mean, it can lock up so easily, and it's not funny. How can you prevent MySQL from locking up? What things can we really do so that MySQL doesn't lock up the entire server so easily?
How To Prevent Stop Words In A MATCH - AGAINST?
I have a simple search engine for my website, and the MATCH-AGAINST works well when search english words (contained in the `name` and `desc` columns), but when try to search a item-code contained in the `sku` column it seems like mysql handle that code (such as "hp-504") as a stop word and returns zero rows...
Prevent Users Creating Their Own Databases
I've installed 4.0.22 and phpMyAdmin 2.6.0-pl2 and followed the instructions, removed the "guest" accounts. Granted users ALL privs on their own db only, which I created. Globally users just have the USAGE priv, no create priv but users can still create their own databases which I need to prevent. What am I missing ?
Prevent One Field Form Overlaping Another
I have the following table: lesson { id int, lesson_start int, lesson_end int, PRIMARY KEY(id) } No lesson can overlap another. So I can't have rows like: id | lesson_start | lesson_end 1 | 12:00 | 14:00 2 | 13:00 | 15:00 Is there any way to deal with it from the MySQL level?
Prevent MySQL From Starting At Startup
I'm starting to play around with MySQL. How can I prevent it from starting up whenever my computer starts? It slows down the startup some, and I'm not always wanting to use it. besides, I can start it myself if I need it.I've had a look on Google for theis, and found quite a few results, but they were fo Linux or Unix. I'm ruinning Windows XP.
Prevent Multiple User Registrations
What's the best way to prevent someone from registering multiple accounts on my website? Should I create a column called "IP Address" in the members table and make it UNIQUE? Are there any complications with this?
How To Prevent MySQL Queries From Taking Too Long
I have a very busy database with some tables having hundreds of thousands of records. I am running MySQL 5.0.16 on Windows. Some of the queries running on my database are not fined-tuned enough, and need improvement. However, the system is live in production and I am looking for a way to prevent MYSQL from committing Harakiri, i.e. from running very slow queries forever, taking the cpu to 50% and beyond for hours. My scripting language is PHP. I found that after a while the end user receives a timeout on the browser, but MySQL continues trying to run the query, sometimes for hours. My question is, is there a way to specify a timeout for MySQL so that it will abort any queries that it is running that have not returned a result by then. The end user is no longer around anyway, so I am looking for a way to protect the database resources (and in parallel I will start investigating how to optimize the slow queries).
How To Prevent INSERT On Duplicate Rows With 30 Fields
I have a MySQL table with about 30 fields. I am inserting new data, and want to be sure that there are no duplicate rows. A duplicate row would be one with ALL the 30 fields (except the auto-increment index) exactly the same. How do I do that efficiently?
Prevent Copying From Mysql Data Tables ?
Recently we had to move some of our databases to the clients site onto their linux server. Can someone hint on how to prevent "the clients" from snatching our data and db schema? The drawback: they do own the machine - hence they do have a root password, therefore even if I change the unix permissions on the mysql directories - they are still open to the "root".
MySQL 4 And MySQL 5 CURRENT_TIMESTAMP Conflict
I've been locally developing a database in MySQL 5. Now I want to put it on my server. In trying to import it, I have come across the following code which is problematic: CREATE TABLE `physical_pages` ( `id` int(11) NOT NULL auto_increment, `filename` text NOT NULL, `created` timestamp NOT NULL default CURRENT_TIMESTAMP, `lastmodified` timestamp NOT NULL default 񟍰-00-00 00:00:00', `languageid` int(11) default NULL, `readonly` tinyint(1) default Ɔ', PRIMARY KEY (`id`) ); It's my understanding that MySQL 4, which is running on my server, does not support the CURRENT_TIMESTAMP part of things. How can I modify that CREATE TABLE query to work? More generally, is there a guide or automatic solution to moving from MySQL 5 to MySQL 4? I am going to have to install this program I'm writing on a number of servers which run MySQL 4, so would like to not have to grapple with manually editing the database dump every time I develop on my machine and update the MySQL 4 machines.
How Do I Prevent "too Many Keys Specified" ?
I keep getting the "too many keys specified" error. I am the admin of mysql and the database that i am trying to run a query in. Which file do i have to edit to prevent this, or is there another query that i must run.
Complex Query - UPDATE Within UPDATE?
Edit: Before anyone leaves this thread, don't be put off by the regular expressions! They are not the problem, so please stay and read. OK, this query has got my head spinning. I am basically creating a query that goes through each product in a table to update the stock for that particular item with that particular size (i.e. I am talking about shoes - different models and each model has different sizes (uk kids 12 -> uk 11). With each shoe it does (or is meant to do) the following: 1. The PHP script that runs the query is looping through every size outside of the query 2. So for each of these sizes it checks to see whether the product it is currently on matches the size it is on 3. When it finds the size it is on, it then deducts the correct number of units from the stock table 4. The final WHERE clause makes sure this subquery inside the UPDATE only happens when the StockUpdated field of the Product table equals 0 (in other words, the stock hasn't been counted before) Basically what I need to do, is first to make sure what I currently have got does the above correctly but also I need the query to UPDATE the StockUpdated field to 1 only when it has been updated successfully. How could I do this? Unfortunately I cannot just add an extra update entry to the end of the query as this would update the StockUpdated field regardless of whether it has been properly counted or not. Here is the query I have so far (with a little simple PHP around it doing the loop): PHP $shoesizes = array(1 => 'ukk12','ukk13','uk1','uk2','uk3','uk4','uk5','uk6','uk7','uk8','uk9','uk10','uk11'); $numshoesizes = count($shoesizes); for($i = 1; $i < $numshoesizes; $i++) { $stockupdate = " UPDATE heelys_stock,items_ordered SET heelys_stock.size_".$shoesizes[$i]." = (SELECT CASE WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?( )?(Kids)?( )?( )?(UK)?( )?( )?(Kids)?( )?( )?[^0-9]12( )?(' -- if UK Kids 12 THEN heelys_stock.size_ukk12 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?( )?(Kids)?( )?( )?(UK)?( )?( )?(Kids)?( )?( )?[^0-9]13( )?(' -- if UK Kids 13 THEN heelys_stock.size_ukk13 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]1( )?(' -- if UK 1 THEN heelys_stock.size_uk1 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]2( )?(' -- if UK 2 THEN heelys_stock.size_uk2 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]3( )?(' -- if UK 3 THEN heelys_stock.size_uk3 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]4( )?(' -- if UK 4 THEN heelys_stock.size_uk4 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]5( )?(' -- if UK 5 THEN heelys_stock.size_uk5 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]6( )?(' -- if UK 6 THEN heelys_stock.size_uk6 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]7( )?(' -- if UK 7 THEN heelys_stock.size_uk7 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]8( )?(' -- if UK 8 THEN heelys_stock.size_uk8 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]9( )?(' -- if UK 9 THEN heelys_stock.size_uk9 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]10( )?(' -- if UK 10 THEN heelys_stock.size_uk10 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]11( )?(' -- if UK 11 THEN heelys_stock.size_uk11 - (items_ordered.Amount/items_ordered.Price) FROM items_ordered WHERE items_ordered.StockUpdated = 0) WHERE (heelys_stock.id = (SELECT heelys_stock.id FROM heelys_stock,heelys_shoe WHERE SUBSTRING_INDEX(items_ordered.Product,',',1) = heelys_shoe.full_shoe_name)) , items_ordered.StockUpdated = 1" // at the moment this last update of the items_ordered table happens to every record!!! even if the other part of query fails // update stock for size $i mysql_query($stockupdate); } Hope someone can see how I can do this? I've been working on this query for 2 or 3 hours now and I've been making reasonable progress but now I am really stumped.
Update Replication, Force Update
Recently an error in the db on my master caused the slave to fail. I noticed this after a few days when I looked at the status and it said "Slave_SQL_Running: No". After looking further I saw what the error was on the master. What is the best method of re-synching the databases? Is there a command to force a re-replication or synch of the dbs? Would you delete the slave's db and update over? In this case, is there a command to pull the data down from the master?
Unable To Update - Have Update Priveledge
I've been granded update priveledge to tables in a few databases on our server: Select_priv ,Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, References_priv, Index_priv, Alter_priv, Create_tmp_table_priv, and Lock_tables_priv are all set to 'Y' for a selected database. I can connnect and read the data and data definitions just fine. I cannot insert, update, delete, create, or alter any table in this database. Our admin worked for a couple of hours yesterday trying to resolve the issue, but no joy. I've googled for this problem and the most relavent posts I've found were problems related with users not being able to connect. I can connect and I can read, I just can't do any of the other priviledges.
ON DUPLICATE KEY UPDATE - Don't Have A Key But Want To Update
This may seem like more of a PHP question but I will come onto the MySQL part in a minute. Basically I have a PHP script that lets users upload 2 different CSV files to different tables. The problem I have is that sometimes these CSV files are updated and then need to be reimported into the database through the script. I have made this script totally generic so that when you change the table that is being entered to the rest of the script will alter itself to import the correct CSV to that table. But a problem arises when I have a CSV file that contains each product sold and the shopper who bought the product (so the shopperID may appear in more than 1 row) - however this file does not have a unique id for each row meaning that when I use an INSERT INTO with a ON DUPLICATE KEY UPDATE... it will just insert more rows instead of updating rows because there is now key in the table. The format of the table is like this (with some example data to show you more clearly): +-----------+-------------+---------+------+--------+------+ | ShopperID | Product Code | Product | Price | Amount | Units | +-----------+-------------+---------+------+--------+------+ 546733 1315 prod 1 64.00 64.00 0 546733 1316 prod 2 43.99 43.99 0 OK just ignore the last 2 fields - that is a problem with the old system there was which does not add the units up right. Anyway basically I might have the same shopper ordering 2 things which will come up on different rows - I have 3 things I cannot do: 1. I can't make the ShopperID field the PRIMARY KEY because I have many times where there are 2 rows with the same shopper 2. And I cannot just do an UPDATE when it gets to a duplicate entry because it will overwrite every time it gets to a shopper that already exists. 3. I don't want to add a unique primary key to the CSV as this will mean that the system I am creating loses its ability of being automatic because the person (an admin) that uploads the file will have to make sure that it has that extra field which is auto incrementing. Does anyone have any ideas on how I can get round this? Here is my PHP function which contains the query: PHP // this function for insert data to csv function makeINSERTS($text, $table, $tablefields, $correctcsv){ $insert = array(); //make array for hold data insert $i = 0; $success = true; while(list($key, $val) = each($text)){ // Insert the data $insert[$i] = "INSERT INTO ".$table." VALUES('"; $insert[$i] .= implode("','", $val); $insert[$i] .= "') ON DUPLICATE KEY UPDATE "; foreach($tablefields as $k=>$field){ $insert[$i] .= "`$field`='{$val[$k]}',"; } $insert[$i] = substr($insert[$i],0,strlen($insert[$i])-1); // echo $insert[$i].'<p></p>' $result = mysql_query($insert[$i]); if(!$result) { echo 'FAILURE to insert/update any or all of the database form uploaded CSV!<br />' $success = false; die('Query failed: ' . mysql_error().'<br />'); } $i++; } if($success == true && $correctcsv == true){ echo 'SUCCESS in uploading the CSV file to the database!<br />' } else { } return $insert; }
Update Queries Do Not Always Update
We are running a MySQL database using PHP to create html pages and forms to update and add information to the db. Sometimes the update query works, sometimes it doesn't - or at least, not permanently. A new record may be added, but five minutes later it has sometimes, but not always, mysteriously disappeared. Where should I look for a solution? Is there a cache that MySQL creates that may be being accessed unwittingly? We are using the standard php and MySQL versions that come as standard on a Mac OS X Tiger 10.4.7 server. The browser we are using is Firefox 2.0.4.
Need To Update PK Of Every Row
I want to update every row's primary key and need an SQL statement to do it. There are 2 columns PK Placename So I firstly want to order all the rows by placename in alphabetical order and then starting by updating the first row to 1, I then want to update every ID with an increment of one - all the way until the final row is updated.
Log Update In My.ini
If i put log-update=update in my ini it works fine,but as soon as try to specify a different location i.e. log-update=/backups/update i cannot access mysql. I have a folder called backups in the data folder and one in the MySQL Server 5.0 folder (just incase), but it still will not work.
Update If?
I have a table that has an item field, a price field, a price min field, a price max field. I want to be able to store the current price for an item as well as it's high and low. Is there a way to do an update on the row for the new price as well as compare the new price to the min field and max field and then update them if the new price is either higher or lower than the current values in the min and max field? I know how to do it with multiple queries and comparing, but am looking for a clean way in one query.
Last Update
Is there a way in mysql to discern when the last date/time a field, row, or table was been updated or inserted?
Can't Update
I'm current using MySQL and ASP. But there seems to be a problem when i try to update a particular record. I get the following error message. Error Type: Microsoft OLE DB Provider for ODBC Drivers (0x80004005) Query-based update failed because the row to update could not be found. Set rs = Server.CreateObject("ADODB.Recordset") sql = "SELECT * FROM MainPageContents " & _ "WHERE BelongsToShop=" & Session("ShopID") & _ " AND ID=" & which rs.Open sql, conn, adOpenKeyset, adLockOptimistic rs.update rs("some_fld") = "something" rs.update I tried setting cursor locations to aduseclient and aduseserver but to no avail. There's also nothing wrong with the query. Does anyone know if there's any kind of issue on updating MySQL using ASP?
Getting A SUM In An UPDATE
I'm trying to update a "parent" table with the sum of data in a "child table". To illustrate, let's say our database was modeled around a book: book -> chapter -> page -> attachment (pictures) The -> represents a one-to-many relationship (e.g. a book has chapters). Each attachment has a filesize and the parent book has a total_size (denormalized) column that is the sum of all it's children attachments. I'd like a query to update total_size in the book table. Below is what I've come up with thus far. Obviously, it's not working, and hence my post. The nested SELECT query works fine, but I'm receiving a "You can't specify target table 'book' for update in FROM clause" error. Code:
Update SQL
I am trying to code a bit of a java app up, and I am sort of learning as I go, so please bear with me for my lack of experence. I set a table in a database using the following syntax: CREATE TABLE Instructor( FirstName VARCHAR(20) NOT NULL, Surname VARCHAR(20) NOT NULL, Title VARCHAR(20) NOT NULL, DOB DATE NOT NULL, Address1 VARCHAR(50) NOT NULL, Address2 VARCHAR(50) NOT NULL, Address3 VARCHAR(50) NOT NULL, PostCode VARCHAR(50) NOT NULL, Telephone INT NOT NULL, Mobile INT NOT NULL, Email VARCHAR(50) NOT NULL, PRIMARY KEY(FirstName, Surname)); I create a record with some information (firstname = emma, surname = newton)... I am then trying to update a record that has been created as follows: Code:
How To Get Key ID For An UPDATE
I need to update a series of fields of a table based on some conditions and get the primary key of affected fields for post-processing the updated rows. Due to the fact that update conditions varies, I do not know what the key field(s) is(are) in advance, so what I want is, when making the update, and in the same transaction, get the primary key of the row that was updated. What I'm doing now is going through a series of 2 queries per transaction, the update and then a select searchig for the updated row(s) with the updated field content. I'm working in PHP 5.1.6 under fedora 6. In a worst case, the update will affect up to 10 rows, so I'll need to get all 10 primary keys... the query I'm running is: $db_query_p3 = "UPDATE message.outgoing SET pool_lock = '$p3_lock', engine = '$engine_id', pool_engine = '$pool_engine' WHERE pool_status='10' AND (pool_lock < '$lock_expired' OR ISNULL(pool_lock)) AND ISNULL(engine) AND ISNULL(pool_engine) LIMIT 10"; but how to capture the primary key? When making an insert the transaction return the key with something like: $insert3 = (@dba_insert_id($db_query_p3))
Update
I want to update 8 rows in a table but these are spaces over the 25 tables. There is a common factor in them all but can't think of a way of doing it.
UDF With Update
I have a lookup table that holds two columns - a key value and another column containing items. For e.g. Col1 Col2 1 10 1 15 1 12 2 20 2 15 Anohter table has to be updated with col1 and the max value of col2 for every col1. So ti should look like 1 15 2 20 .... Right now i have a sQL statement that gets the max of the first table and then does an update on the second. I was thinking about making it a single query. Multi table update doesnt work here as the order of updates is not guaranteed. So i wrote a UDF that will get the maximum value of a column for any table. So if u call maxfun('Tab1', 'Col2', 1) it will return 15(The maximum value of col2 in Table1 where col1 = 1). When i use this in my update update table2 set column = maxfunc('Tab1', 'Col2', 1) where column2 = 1; hangs mysql. Has anyone tried UDF with update statements?
Update
Is it possible to do update a row with out first doing a separate query... sort of like += in perl or the sum() function?
UPDATE
I have in table 2 rows with same record only field 3 and 4 is different. field 1, field 2, field 3, field 4 _______________________________________ salcin, elva, 23, 0 salcin, elva, 0, 45 so how can I update or... to have only one row with record like: field 1, field 2, field 3, field 4 _________________________________________ salcin, elva, 23, 45 So just to get 1 row and where is value 0 and field 1 and 2 same to get "new" value
UPDATE
I have in table 2 rows with same record: field 1, field 2, field 3, field 4 _______________________________________ salcin, elva, 23, 0 salcin, elva, 0, 45 so how can I update or... to have only one row with record like field 1, field 2, field 3, field 4 _________________________________________ salcin, elva, 23, 45
Update
"UPDATE `ItemInformation` SET `LastItem` = 0, `ItemUsed` = 0 WHERE `GameNumber` = $row[0] AND `TeamNumber` = $row[1] AND `LastItem` = 1"; With this code, it's updating `LastItem`, but not `ItemUsed`. Can anyone point me towards a indication as to why?
Update 5.0 To 5.1 Or 6.0 ???
i just upgraded to latest mysql 5.045, should i go to 5.1 or 6.0?? is it worth the pain, do i need to change passwords format like from 4 to 5?
Update
I have about 1000 updates required on a table. The syntax i know is for example Update t1 set b_id=9264 where b_id=4259; and then the next one would be Update t1 set b_id=9358 where b_id=2253; etc etc Is there any way i can run an update with multiple instructions?
UDF With Update
I have a lookup table that holds two columns - a key value and another column containing items. For e.g. Col1 Col2 1 10 1 15 1 12 2 20 2 15 Anohter table has to be updated with col1 and the max value of col2 for every col1. So ti should look like 1 15 2 20 .... Right now i have a sQL statement that gets the max of the first table and then does an update on the second. I was thinking about making it a single query. Multi table update doesnt work here as the order of updates is not guaranteed. So i wrote a UDF that will get the maximum value of a column for any table. So if u call maxfun('Tab1', 'Col2', 1) it will return 15(The maximum value of col2 in Table1 where col1 = 1). When i use this in my update update table2 set column = maxfunc('Tab1', 'Col2', 1) where column2 = 1; hangs mysql. Has anyone tried UDF with update statements?
Update
I have an address database "address", and I need to update the field "street" row by row for some reason, then when I use update (select street from address limit 0,1) set street="High Road"; I have a error nor update address set street="High Road" where Street= (Select Street from address limit 0,1).
Update 4.x To 5.0
Has anyone made this update? I'm running on FC4 for our corporate intranet and am running the non-rpm distro of MySQL 4.1. What is the easiest way to update to 5.0? I don't really see any update scripts or anything to make this update? Is it as easy as untarring the package - installing it just like I did 4.1 and then copy the data files over (certainly it can't be that easy?
Update
[MYSQL] "UPDATE `ItemInformation` SET `LastItem` = 0, `ItemUsed` = 0 WHERE `GameNumber` = $row[0] AND `TeamNumber` = $row[1] AND `LastItem` = 1"; [/MYSQL] With this code, it's updating `LastItem`, but not `ItemUsed`. Can anyone point me towards a indication as to why?
Update
I want to update the country field in my clientinfo table with phpadmin where country is empty. The field is: Name:country Type:varchar(250) latin1_swedish_ci Atributes: - Null: Yes Predetermined: Null What is the mysql to do that?
Update
I have two tables status and confirm status. Status has these fields authno,user,status confirm_status authno,user,status now i want to do a update script that updates the status in the status table based on matching authno from confirm status??? so im trying to update the status field in the status table based up matching authnos from confirm_status and the status table??
|
TRACKER
