UPDATE Works In MS SQL
I use the following stament work with MSSql, but not MySql, why ?
Update mytable set mycol = 'my_Text' + mycol
Failed, mycol is varchar
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Can't Update Multiple Rows Via PHP, But Works In PhpMyAdmin
My php script will update 1 row fine, but fails on 2. However, the exact same query works when pasted into phpMyAdmin. THIS QUERY WORKS BOTH IN MY PHP AND PHPMYADMIN: $sql = "update table set color='red' where id='1';"; $sql_do = mysql_query($sql); THIS QUERY WORKS ONLY IN PHPMYADMIN: $sql = "update table set color='red' where id='1'; update table set color='green' where id='2';"; $sql_do = mysql_query($sql); I know the second query is correct, because when I copy it from my php code and paste it into phpMyAdmin, it works. But only the first query, with 1 row update, works from my php code.
Works
I have this and it works just fine but I cant help but think there is a much better way of accomplishing the same thing in one sweep verses 6 PHP Code: mysql_query("update residential set bed='1' where bed like 'ONE%'"); mysql_query("update residential set bed='2' where bed like 'TWO%'"); mysql_query("update residential set bed='3' where bed like 'THREE%'"); mysql_query("update residential set bed='4' where bed like 'FOUR%'"); mysql_query("update residential set bed='5' where bed like 'FIVE%'"); mysql_query("update residential set bed='6' where bed like 'SIX%'");
Works In MYSQL 4.1 But Not In 5.0
MySQL 1054 - Unknown COLUMN 'p.manufacturers_id' IN 'on clause' SELECT count(*) as total FROM products p, categories c, products_to_categories p2c LEFT JOIN manufacturers m on (p.manufacturers_id = m.manufacturers_id), products_description pd WHERE c.categories_status=1 AND p.products_id = p2c.products_id AND c.categories_id = p2c.categories_id AND p.products_status = Ƈ' AND p.products_id = pd.products_id AND pd.language_id = Ƈ' Here is the query in PHP PHP "select p.products_id, pd.products_name, p.products_image, p.products_price, p.products_tax_class_id, p.products_date_added, p.manufacturers_id, m.manufacturers_name from " . TABLE_PRODUCTS . " p, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_MANUFACTURERS . " m on (p.manufacturers_id = m.manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd where c.categories_status=1 and p.products_id = p2c.products_id and c.categories_id = p2c.categories_id and p.products_status = Ƈ' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' order by p.products_date_added DESC, pd.products_name";
How Exactly The LIKE Keyword Works?
I have a table named Inventory and have 3 items in it containing the work w069. I want to run a query to return the three items. My query: Select * from Inventory where Description Like 'w069' It doesn't seem to work, returning zero row.
UNION - Works In 4.0.16, Not In 4.1.8
I have a query that works in 4.0.16 on my web host, but not 4.1.8 on my dev. box. Web host is upgrading in 10 days. Notes: Server language is ColdFusion gamedate is type DATE SELECT DISTINCT gamedate FROM schedule WHERE gamedate >= now() UNION ( (SELECT DISTINCT gamedate FROM schedule WHERE gamedate < now() ORDER BY gamedate desc LIMIT 1) ORDER BY gamedate asc LIMIT 3 The goal is to select the most recent gamedate prior to "today" and the next two game dates including "today".
Query Only Works Once
I'm using jsp with mysql and am querying the database for data. Everything works fine on the first try. If I click back on my browser and hit submit again on my form, I get no data. I've tried removing the close() commands but that doesn't seem to work.
SSN Changes In Database But Still Works As Key?
I have a ssn field in a table but somthing happens to the data. I use an update to enter the data and set the value to 123-12-1234 for a test but when I look in the table or I get the record from a record set it come out like -1123. Why is this? But when I run a query and ask it to return all records which have a ssn of 123-12-1234 it returns the correct record. If this is correct then how can I display the ssn number in a java text field.
How Exactly The LIKE Keyword Works?
I have a table named Inventory and have 3 items in it containing the work w069. I want to run a query to return the three items. My query: Select * from Inventory where Description Like 'w069' It doesn't seem to work, returning zero row.
How Decimal Works
Say that we have an variable that is var1 = 3 var2 = 3.65 var3 = var1 * var2 '--- Sum = 10.95 Now my Question! How do I input this variable into MySQL in a COL whit DECIMAL (10,2)? I only get the result 10.00 in the COL? It seams that MySQL round every resout I get to .00 at the end. If I make an varible hard ex. var3 = "10.95" that works but if I convert an numeric variable to string it doesnt?!?!?! ex. var4 = cstr(var3)
MySQL Delete: Works In Php 5 But Not Php 4?
I've developed a small login system using Xampp on my local machine. Everything is running great except when the admin tries to delete something. Apparently I have the wrong syntax for my deletion in mysql. Here's my original delete statement (from mysql5): DELETE at.* FROM access_table AS at WHERE at.client_id=2 And here's the error I get from mysql4 (I ran the command in phpmyadmin console): #1066 - Not unique table/alias: 'at' So I removed the aliases and re wrote my command as such: DELETE * FROM access_table WHERE at.client_id=2 And got the following error from php4: #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '* FROM access_table WHERE at.client_id=2' at line 1
SQL Query Works Only In Win32!
I use MySQL from my php page and from phpMyAdmin. In Windows Server, following query works fine. In Linux Server instead it returns error. The SQL Query is: SELECT DISTINCT a.id, a.container, b.container FROM folder AS a JOIN folder AS b ON a.container=b.id The PHP error is: You have an error in your SQL syntax near 'ON a.container=b.id' at line 1 So, I ask: If in Windows this query work fine, where is the error in the Linux version?
SHOW DATABASES Works In 4.0.14
I've been trying to solve this problem for a long time. Only thing I did when upgrading from 3.25 to 4.0.14 I took the default my.cnf. I did not convert the privilege tables with mysql_fix_privilege_tables. Suddenly, the mysql started to show all databases to all users (even though the user had access denied and could not do show tables on them). So I started to find help and tried the mysql_fix_privilege_tables. It didn't work properly (more than just access denied and errors, that are ok) so I did the changes manually conforming to source code of mysql_fix_privilege tables. PhpMyAdmin stopped its warning about privilege tables not conforming to current mysql version. Everything seemed ok except that SHOW DATABASES still worked. The users naturally all have Show_db_priv="N" in the tables. Also trying "safe_show_database", "skip_show_database" in my.cnf and "--skip-show-database" witch mysqld didn't help.
JOIN Query Works With 3.23 But Not 4.0.13
I have a simple join query below which queries the name of schools with count of student numbers for each school from two tables School,Student. select sc.name, Sum(if(student.SNO is not null, 1, 0)) nmbr, from school sc left outer join student on sc.school_id = student.school_id group by sc.school_id this runs just 0.30 sec with Mysql3.23 but 30Sec with Mysql 4.0.13. i could not find a solution.But i suspect from NULL threatment.
Parentheses: Query Works With 4.1.7, But Not With 4.1.11
Since we updated to the newest MySQL version (Windows) some of our queries do not work any more. It's reproducable by this (dummy) query: SELECT B.ID from ((SELECT * from A) AS T1 INNER JOIN B a ON B.ID = T1.ID); This works with 4.1.7, but not with 4.1.11, unless the parentheses are removed: SELECT B.ID from (SELECT * from A) AS T1 INNER JOIN B a ON B.ID = T1.ID; Does anybody know why MySQL changed its behaviour that way? I know the parentheses in the first query are not necessary, but it would be quite difficult to search through all our queries for unnecessary parentheses.
SHOW DATABASES Works In 4.0.14
I've been trying to solve this problem for a long time. Only thing I did when upgrading from 3.25 to 4.0.14 I took the default my.cnf. I did not convert the privilege tables with mysql_fix_privilege_tables. Suddenly, the mysql started to show all databases to all users (even though the user had access denied and could not do show tables on them). So I started to find help and tried the mysql_fix_privilege_tables. It didn't work properly (more than just access denied and errors, that are ok) so I did the changes manually conforming to source code of mysql_fix_privilege tables. PhpMyAdmin stopped its warning about privilege tables not conforming to current mysql version. Everything seemed ok except that SHOW DATABASES still worked. The users naturally all have Show_db_priv="N" in the tables. Also trying "safe_show_database", "skip_show_database" in my.cnf and "--skip-show-database" witch mysqld didn't help. All *.frm files in mysql database have the same md5 as those generated by scripts/mysql_install_db.
Exporting MS Works For MySQL
I have a MS Works database and I can't find anything useful on the PHP site for working with this type of database. I did a quick search and it seems that it can be exported for OoenOffice.org and other spreadsheet database programs but none of them give a method. Mostly selling books and MS Access. If there is a way to access the .MWD or .DBF Microsoft works files with PHP that would be great. If that's not possible, preferably not, I'd rther switch to MySQl easily if possible. I'm still looking but it's not looking good.
Mysql.exe Sort Of Works
I have just installed MySQL (not an easy thing to do). I also have installed PHP. I use Dreamweaver8 for 2 static sites, and eventually will figure out how to use that with PHP/MySQL. Everything seems to work fine, and I'm working my way through Janet Valade's book, "PHP and MySQL for Dummies." Everything I have tried in the book works perfectly except for the mysql.exe command. I am in the correct folder and type mysql.exe -u root -p and am prompted for my password. When I enter my password the cursor moves to the next line and just sits there. Nothing happens. Fortunately, the window is not frozen and I can just close it.
Query Works With One Record
This query works great if there is one matching record in the Albums table. When there's more than one matching record it returns nothing. Code: SELECT Artists.Artist, Albums.Album_Name FROM Artists, Albums WHERE SOUNDEX('colname') = SOUNDEX(Artists.Artist) AND Albums.Artist_ID=Artists.Id
GROUP_CONCAT Problem In 5.0.24 (query Works In 5.0.18)
So on my production server they are running MySQL - 5.0.24-standard. Locally I'm only running 5.0.18 but didn't figure it would be much of a problem, until I noticed that my "results" from certain searches were different with the exact same data on each MySQL version. The query is this:
Query Help. This Works, But I Don't Think It Is Good Practice
comics: id date location comments: id comicid datetime header comment author Every comic can have multiple comments. Each comic will have a comment, but there is a period of time where the comic is posted but the first comment is not yet submitted. So for argument's sake we can't assume there is any comment at all for a given comic. I want the all the comic ids and dates before a certain date along with the most first comment entered relating to that comic. select comics.date , comics.id , comments.header from comics left outer join comments on comments.comicid = comics.id where (comics.date>=񟭇-03-01') group by comics.id I never defined WHICH header should be returned when many are grouped together. I just know that mysql will return the first one in this circumstance. I seem to recall reading somewhere that different databases handle this sort of thing differently, with some returning a null in that field? I keep trying to forumate a useful subquery but keep drawing blanks just as I think I have it figured out in my head. Like I forget how the string starts by the time I mentally formulate the end....
Suddenly A Script No Longer Works
I always get the following message when starting a search in my database: Search for titles in the mylibrary database. Search for titles beginning with a:
V. 4.1.7 + Php4 Works, + Php5 Problems
I am running: Mac OS X 10.3.7 apache 2.0.52 MySQL 4.1.7 php 4.9.3 & php 5.0.3 I have an html form that "POST"s data to a db. I have a "GET" script to repopulate form for editing. Everything works fine with php 4.9.3. With php5.0.3 the data is "POST"ed just fine (I used a different GUI app to check) When I try to "GET" said data, form is returned empty? I have all error reporting turned on = nothing. I have all backwards-compatibility directives in php.ini config file turned on.
Two Different Websites Connected To One Database But Only One Works...
I have been recently having problems transferring my website from one domain name to another. The first domain name was used whilst at uni, but the second is my own perosnal website. One the original website, the MySQL database allows the user to login and register new users without any problems. However, the second website does not allow me to do this. I would consider myself a newbie when its comes to MySQL so I was wondering if the community could help me out on this one. Both websites are hosted by the same company, and both websites use the same database but my limited knowledge cannot solve this problem. Are there certain permissions I need to set or something? Can anyone help me out on this one?
Delete Using Multiple Table: Works On 4.1.20, But Not On 4.0.16
I've a simple delete query delete from joingrp as J using joingrp as J,msggrp as M where J.msgid=M.msgid and M.parent!=0 When I tried it on phpMyadmin at ixweb, having mysql-4.1.20, it works as desired. But when I tried it on phpMyadmin at iPowerweb, having 4.0.16, it says #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'as J using joingrp as J,msggrp as M where J.msgid=M.msgid Can I solve this without using any server-side loops?
My MySQL Back-end Works Very Slow
I have installed my MySQL Server in a Window XP platform running in a Pentium4 machine. When I access the database on the machine, it is working in a normal speed. But when I access the database from a client machine (still running in windows platform), it runs very slow.
Quering Works Except Need To Sort Results By Other Table...
I have the following query: SELECT plugin_id FROM tc_plugin_lists WHERE (page_id='1' AND position='0') ORDER BY tc_plugins.sort_number ASC; This almost works, the problem is the ORDER BY clause. It should order the results by the sort_number field from another table called tc_plugins. Now it throws an error... SELECT items FROM other table ORDER BY other tables field values where field is same as in 'items' ASC ?
Using "WHERE LIKE '%Word'" That *will* Use An Index... Works But Feels Stupid!
FYI, I'm using INNODB. Let's say I have a table with a city column containing 30,000 records, and I want to provide a search form 'starts with' and 'ends with fields. So a user would supply the following: Starts with: Cha Ends with: tte And the search would find a record such as 'Charlotte' The 'starts with' search can use an index (eg, LIKE 'Cha%'), but ends with can't (eg, LIKE '%tte'). A solution seems to be to create a city_reverse column, add a unique index on that, then perform an 'ends with' search on the city names in reverse. For instance, the 'Charlotte' record would be stored in the city_reverse column as such: 'ettolrahC' ...and I'd perform an 'ends with' search using the unique index: LIKE 'ett%' The above works, but it feels stupid, and adds weight to the database. Is there an alternative?
Error With SQL Statement When Using MySQL 5.0.24 (Works Fine In MySQL 4.0)
Here is my query that worked in mySQL 4.0 but not in MySQL 5.0.24: INSERT INTO magtallynew SET magempnum=?', magcentre='YVRRC', entrydate=��-08-15', empnum=?', empcentre='YVRRC', connection='Rockwell', service='Express', region='dom', callertype='CSR', otherdept='', transferredcall='No', directrespdept='No', station='Not Applicable', withoutmagassist='Yes', category='Accounting', issue='Advised On Contact', comments='test' Here is the error message I get: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'connection='Rockwell', service='Express', region='dom', callertype='CSR', otherd' at line 1
UNION, Individually Works Fine, But Not In Union
Well, I am using an UNION, and while both of the queries works fine while used individually, they don't work while in the UNION. Here is the code - PHP ( SELECT * FROM job WHERE date_fin !=0000-00-00 ORDER BY date_fin ASC ) UNION ( SELECT * FROM job WHERE date_fin =0000-00-00 ORDER BY date_creation ASC ) What do u think guys? Note : MySQL version 4.1.21 Note : Not working means, this part is not working => 'ORDER BY date_fin ASC', means, the returned results are not ordered as I want. MySQL doc. tells, To apply ORDER BY or LIMIT to an individual SELECT, place the clause inside the parentheses that enclose the SELECT: (SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10) UNION (SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);
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.
User Problems... Only Root User "works".
i have been trying to figure this strange problem out for a week or so. here it is. i have a set of users with various permissions for different reasons. about 2 weeks ago, my hardware suffered a fairly nasty failure. i recovered everything. things seemed to work out. i didnt think to check my phpBB forums or my blog (or other mysql based stuff). never had any problems with the DB... so, maybe i am missing something simple here. i hope so. what my users can do: they can log in via command line, a gui i use on mac os x and it does not matter if it is local, or remote. they work just fine. what all but one users cannot do: none of my users can run phpbb or the blog stuff.. except for the root user. any other user returns the error message written in the code of which ever script i try to access telling me that "could not connect to DB" etc.. root works fine. now, i thought maybe it was a problem with the forums and other stuff. but i cant be. same thing happens with all things... even a game server i occasionally run that keeps account info in a DB. all these users are set up properly and have worked just fine in the past. nothing new has been installed, no conf changes..im at a loss...
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
|
TRACKER
