Update One Field With More Fields From Another Table
I have a select count() that seams be ignoring one clause. Data:
Table:
ID CA-Char CB-Int
1 dsfsd 6
2 dsfsd 0
3 dsfsd 1
4 sdrtt 1
SQL is:
"SELECT count(id) FROM Table WHERE CA-Char = 'dsfsd' AND CB-Int > 0"
Returns 3 - Should be 2
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
How To Update All Fields With A Multiple Of The Field Value?
Is there a MySQL command to replace all the values of a field with the field's original value that's had a mathematical formula applied to it? Basically, I want to divide the field's original value in half, then add 40% more. The formula would be y=x/2+.2x where y is the new value and x is the original value. I know I could write a PHP script that would select each field's value, apply the formula, then update the new value, but it'd be cool if there's a MySQL built-in function to do this.
Fields In Table Will Not Always Update,
I'm using C#, ASP.NET and MySQL, The language is no problem (not to much) but the rest well I wonder. I have a DB table that has 5 seperate fields. A main one that is Integer and the rest are VarChar(50). each of the 4 others are identical in setup. I can change some records and others I can't. ID int(10), std varchr(50), atd varchr(50), dtd varchr(50), ctd varchr(50) I update say 'atd' with new data, Date, Time and a code of 15 letters/numbers.This one takes, I change to another record and try to update ctd and it shows like it takes but when I use the Command Line it shows no updates on ctd. this is random on this also. If I change to another record it may all work or not. Any Ideas at all?
Update Fields In Table
I am trying to update multiple records,rows,fields in a table. Below is what I am placing in the sql field in phpmyadmin. It works with one update line but not more. What am I missing here? UPDATE Products SET TitleTag='U.S. Air Force Retired Shop On-Line for Flags of the world - US, International Flags ' Where Product_ID=28; UPDATE Products SET TitleTag='Vietnam Veterans of America Shop On-Line for Flags of the world - US, International Flags ' Where Product_ID=29;
Updated Table Fields Rolling Back After Update
I'm having to correct some scripts written by a collegue who is presently not around. I had to update 2 fields with over 13000 rows. The update do work well but the problem is that somehow, the initial values in the fields do roll back in again - kind of auto updating to the former values itself. Any ideas what could be wrong? Any way round it?
Update Comment In Field In Table?
Create table images ( UserID Int UNSIGNED NOT NULL, ImageID Int UNSIGNED NOT NULL AUTO_INCREMENT, Name Char(50), Description Text, DateModified Datetime, Active Bool, Private Bool COMMENT 'This field is used to denote an image that should not be available in the global search engine for photogap', Type Smallint COMMENT '0 = jpg 1 = gif 2 = bmp 3 = ico 4 = png 5 = psd 6 = flash Others can be added if needed.', Primary Key (UserID,ImageID)) ENGINE = MyISAM ROW_FORMAT = Default; And say I wanted to update the COMMENT on the field 'Type' how would I do it? Right now that field says COMMENT '0 = jpg 1 = gif 2 = bmp 3 = ico 4 = png 5 = psd 6 = flash Others can be added if needed.' But I need to change this as things have been reworked and added and these numbers do not match up anymore.
Update A Table In A Timestamp Field
I would like to update "field A" in a table in which "field B" is a timestamp. I would like to update all fields whose date is less than a given date. This is the command I was using to no avail: UPDATE my_table SET Field_A=50, WHERE filed_B <= '2005-05-12 15:08:44'; MySQL is returning the following error: #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 'WHERE filed_B <= '2005-05-12 15:08:44'' at line 3 My Host is using MySQL 4.0.25. Can anyone point me in the right direction? What am I doing wrong?
Update One Table Field With Another Table Field
i am having a problem in my update function. I have two tables: buyselldata table and autozack_signal table buyselldata bsignal coloum has to be updated with Close_Price in autozack_signal table if macd_buy in autozack_signal is 1 common field in both tables are Script_Name "Update buyselldata b set b.buy = a.Close_Price where exists(select Close_Price from autozack_signal a where a.script_Name = b.script_Name and a.macd_buy=1)"; i am getting -1 for mysql_error()
4.1 - Update A Field In Table1 With Total Aggregate From Another Table
I want to update table1.field3 with the SUM() of table2.field4 where table1.id = table2.table1_id So I need to agrregate table2.field4, get the sum where table1.id = table2.table1_id. then take that total and put it into table1.field3 Can you do that in one statement or a series of statements - all using DML?
Copy Field Over, Then Update The Original Field
I want to change a price of a product, but back up the old price. I was trying to do this through two queries, an INSERT and an UPDATE, but it's not working. INSERT the row that will store the backup: INSERT INTO s01_MUS_SalesXProducts ( s01_MUS_SalesXProducts.sale_id , s01_MUS_SalesXProducts.prod_id ) VALUES ( ".(int)$sale_id." , ".(int)$prod_id." ) Then I would copy the prices over and update the original with one UPDATE query: UPDATE s01_Products LEFT JOIN s01_MUS_SalesXProducts ON s01_Products.id = s01_MUS_SalesXProducts.prod_id SET s01_MUS_SalesXProducts.old_price = s01_Products.price , s01_Products.price = ".(float)$sale_price." WHERE s01_Products.id = ".(int)$prod_id." However it is copying the new price to the backup field. Is there a way to do this in two queries? I know if I select the product price before inserting I can do it in 3. Truely I would like to do this in 1, but I don't believe this is possible due to the nature of the queries.
Fields In One Table Overwrite Fields In Another Via JOIN
I'm writing a simple web game which uses mysql to store data. The prototype works ok so far, but now I'm refactoring it to support multiple players. This means coming up with a way to store the player's current world state. I'm thinking of doing this by having a table that defines the initial world state, and then a table containing the 'state' of anything different. Example: The 'objects' table looks like this: object_id, name, room 1, Hammer, 1 2, Mirror, 1 3, Spade, 2 So, at the start, room 1 contains a hammer and a mirror, and room 2 contains a spade. Now, if player 12 picks up the hammer then drops it in room 2, I store this override in the 'state' table player_id, object_id, room 12, 1, 2 So. I'm trying to see if there's any kind of join syntax that would return all the objects in a room for a given player's 'session'. Obviously it could be done with temporary tables, or just comparison outside of SQL, but I keep thinking this might be possible *somehow*
Update Multiple Fields
I need to update some 900,000 records, each different. I will be pasting the code into the sql window of PHPMyAdmin at my web site. The individual updates look like this: UPDATE table SET Field1='sample', Field2='sample1' WHERE UniqueID=12345 UPDATE table SET Field1='sample2', Field2='sample3', Field3='yes' WHERE UniqueID=2021432 How can I string together a bunch of these where the SET field values are always different and there is one each for 900,000 different ID's. Right now, I have these in 30 text files but I can't seem to get the syntax correct.
Update Multiple Fields With 'OR'
Is there a better way to do this (more efficient). I have up to 200 items to update per query, the id's are not sequential. Using up 200 (well 199) OR's in a single query doesnt strike me as being the best way to do this. All the fields get set to the same value. # Example UPDATE table SET value=value WHERE id=11 or id=23 or id=31 or id=74 or id=56 or id=96; # Up to 200 fields Or am I stuck with this query? (which works).
Help With UPDATE Using CONCAT On Longtext Fields
I have two tables with longtext fields that I'd like to concatenate and update in one of the tables. Not sure if you can use concat on a longtext field. Here's what I tried: UPDATE `node_revisions`, `weblink` SET `node_revisions`.`body`=concat(`node_revisions`.`body`,"<br /><a href="",`weblink`.`weblink`,"">Visit this site</a>") WHERE `node_revisions`.`nid` = `weblink`.`nid`; This is the scenario. I have these 2 tables: node_revisions table ----------------------------- | id | nid | body (longtext) | ----------------------------- | 1 | 23 | this is some text | ----------------------------- weblink ----------------------------- | id | nid | weblink (longtext) | ----------------------------- | 5 | 23 | http://somesite.com | ----------------------------- I'm trying to use UPDATE to concatenate the node_revision.body and weblink.weblink fields, where they share foreign key nid. So the resulting node_revisions would look like: node_revisions table ----------------------------- | id | nid | body (longtext) | ----------------------------- | 1 | 23 | this is some text<br /><a href="http://somesite.com">Visit this site</a> | ----------------------------- When I run the UPDATE statement above, I get the following feedback, but nothing has in fact changed: Query OK, 813 rows affected (0.15 sec) Rows matched: 813 Changed: 813 Warnings: 0
Extend Fields With UPDATE Clause
I don't know why MySQL handles the UPDATE-query not in that way it should be. To show the problem we assume to tables: DROP TABLE IF EXISTS temp1; CREATE TABLE temp1 ( id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, text1 VARCHAR(45) NOT NULL, PRIMARY KEY(id) ); INSERT INTO temp1 Values ( 1, "" ), ( 2, "" ), ( 3, "" ), ( 4, "" ), ( 5, "" ); DROP TABLE IF EXISTS temp2; CREATE TABLE temp2 ( id_fk INTEGER UNSIGNED NOT NULL, text2 VARCHAR(45) NOT NULL ); INSERT INTO temp2 Values ( 1, "1-1" ), ( 1, "1-2" ), ( 2, "2-1" ), ( 2, "2-2" ), ( 3, "3-1" ), ( 3, "3-2" ), ( 4, "4-1" ), ( 5, "5-1" ); Now I want to update temp1.text2 with all referenced values from temp2.text2 UPDATE temp1 INNER JOIN temp2 ON id = id_fk SET temp1.text1 = Concat( temp1.text1, ',', temp2.text2 ); The table temp1 should (actually) have this content: SELECT * FROM temp1; id text1 1 ,1-1,1-2 2 ,2-1,2-2 3 ,3-1,3-2 4 ,4-1 5 ,5-1 But when I look at the table: SELECT * FROM temp1; id text1 1 ,1-1 2 ,2-1 3 ,3-1 4 ,4-1 5 ,5-1 In doc is mentioned that every line is updated, but he doesn't.
UPDATE To Correct /n In Text Fields
Not sure if this can be done in MySQL or if I need do dump the database and correct it in a text editor. I have a text field in my database which got pulled off of another db somewhere in the transfer line breaks became /n and they are in the text as /n can I run an UPDATE which will actually check the text field for /n and convert it to a <return>. The db is not massive so I can always dump it and run it through bbEdit if need be
One Table With Many Fields Or Many Tables With Few Fields?
I need to build a database, but I'm torn between these 2 choices: Is it better to have one table which has many fields or many tables which each has few fields? Is it true that the latter is worse because it will require many join operations? What is the limitation of the first option (one table with many fields)?
Sort By 2 Fields Unless One Field = 0
Quick recap - I know very little about databases. I'm trying to produce a query for a MySQL table. The table has 3 columns of interest: itemid - a unique integer representing this item lastcommentid - a unique integer which is the unique id of a row in another table itemname - free text name I want to select all the items in this table, ordered by the following logic: Quote: - If the lastcommentid is not integer 0, order the results ascending by lastcommentid (lowest lastcommentid first) - However, if the lastcommentid is integer 0, order the results ascending by itemid - Regardless of the above 2 points, only return items with an itemid higher than N. When done correctly this should return records ordered by their last activity, because either: A comment was made on the item in which case its lastcommentid will be greater than other items' lastcommentid, or, No comment was made on the item but the itemid field is greater because the item was added after other items.
Adding A Field Between Fields
If i had the following list of fields in my database: MemberID Surname DatofBirth How would I add FirstName between Surname and DateofBirth?
Multible Fields In A Field?
I am making a gaming site that is PHP/MySQL drivin. I put all the codes, games, basically any informaton in the database, and use PHP to take it out. Well, I've been workking on this little project for about a week now, and it just dawned on me. If I can only get one cheat code in per field, how am I going to add multiple cheats. I use one row for easch game title that I am going to use for my site, so, that means that if I wanted cheat codes, I would have one colomn for each cheat. But I can't do that, because I want to type in one code for the "code page" (where the cheats are viewable after the user clicks on the the game title), so that all the codes show up. Code:
Combining UPDATE Statements With Different Fields And Conditions
I'm slightly paranoid that I haven't unearthed an existing answer to this question, but the ones I have been able to find didn't have two different variables requiring two different conditions. Basically, I want to know if it's possible to combine the following UPDATE statements into a single query: UPDATE table SET field1 = field1 - n WHERE field 1 > x UPDATE table SET field2 = field2 - n WHERE field 2 > x .......
Selecting Fields Based On The Value Of Another Field
My friend asked me to help with this query but unfortunately I wasn't able to figure it our for him. Here's the info he presented to me: Table 1 - portrait field 1 - id field 2 - type (will be the id from a row in business or personal) Table 2 - business field 1 - id field 2 - bizname Table 3 - personal field 1 - id field 2 - firstname field 3 - lastname His PHP code will select a row from portrait where 'id' is a given number. Then based on the value of 'type' for that row he will either select the 'bizname' for that corresponding id or 'firstname' and 'lastname'. There is no way to know ahead of time whether the row is a business or personal account. If this can be done without a subquery, that is ideal. However, if a subquery is required, that's ok, too.
Display All Fields By Calling One Field
I created a testing db with three fields: id, title, text, where id is auto increment. I have this on my .php page: $dbh=mysql_connect ("localhost", "username", "password"); mysql_select_db ("testing_db"); $result = mysql_query("SELECT id FROM demoTable"); while ( $row = mysql_fetch_array($result) ) { echo("<P>" . $row["id"] . "</P>"); } this only displays all the id of each row of data. the way i want it to work is to call the id number (eg. page.php?id=1) and then display both the title and text defined for that id. i reckon this is a php syntax problem...would i hv more luck posting in the mysql forum?
Adding A Date Field, Need To Populate The Fields
I have a table and until now there is was date entry for the fields. I have added a timestamp (column "time") and from now on, every time I insert a new entry, the date/time will be recorded. The thing is, I want a date/time to exist for the old fields. I have about 250 fields and I want each field to be assigned a timestamp based on the fields alphabetical order (ordered by "title"). So, field with title "aaaaa title" will have a timestamp of today. A field with a title "zzzz title" will have a timestamp of 250 days ago. It doesn't have to be exactly like this. My main goal is to assign dates to my old fields. Anyone know how to do this?
Combining Date And Time Fields Into A Datetime Field
I have a table with separate Date and Time fields. Is there a way to either: 1. Convert the date and time fields into a combined datetime field? or 2. Create a datetime value in the select statement so I can select all records before a given date and time?
What Field Type To Use To Avoid Blank Spaces In Fields
Can you tell me the best field type to use here? I've got a table in mysql with all 5 fields defined as tinytext Problem is when I export this to to a text file for notepad each field is padded out by several blank spaces, and i think my eamil program doesnt like this type of structue : field1 , field2 , field3 , field4
Help With Moving Data From One Table Field To Another Table Field
I am a relative newbie at this so would appreciate help - already searched and found and tried several suggestions for similar issues - but nothing quite worked! I need to move a membership roster to another table in the same db - currently the data is at jos_users1 and it needs to be moved to jos_users. The jos_users1 table has only one field, named email. The jos_user table has several fields including the email field, and already has data in it - so I don't want to overwrite the table - just upload the additional email addresses into the table.
Retrieve Field Value When Submitting To Table Auto Increment Primary Field Value
In my form, I write to four fields in the table, (fields 2,3 4 and 5), which creates a new record. the first field of this new record is an auto-incremental field, giving a unique id. How can I write to the table and at the same time retrieve that unique id accurately, even if more than one person is performing write tasks simultaneaously? My first thought is that if I grab the previous records id and add 1, then this may not match my newly inputted record, given that it may take a few minutes to add the record and someone else could have been there before me with a different record. (I hope that makes sense). Then I thought that on opening the form, it could write to a new field inputting my loginID. Then I can retrieve the unique auto-increment number before then filling out my form and over-writing the loginID that I had previously entered. Sounds too convoluted to be the best way.
Update A Field Only On A Bit
I have a table "state" and I use each bit to store a flag 'YES' or 'NO'. I'd like to update this field but bit per bit.
Can't Update A Field From VB
I've just setup MySQL on a Linux machine and I'm using Visual Basic in Windows to connect to it (using MySQL ODBC 3.51 driver). Everything works fine for browsing the database (used for stock control), but some parts of the VB program refuse to update the database. It was using an Access database (which worked ok) and I'm using the same SQL statement and table structure. Code:
Update Field
Need your help as I have difficulty in updating field in mysql. My problem like this. I want to update data of customers where their expiredate is on 20071231. Then I use below command to retrieve the total customer where their expiredate 20071231: select username from custdb where expiredate='20071231'; The result is it retrieved about 750 customer's username. The problem is, how can I update this 750 customers expiredate without update one by one or using IN?
Update One Field
I'd like to update one field in a table with a substring of another field in the same table. Can anyone show me an example of how to do this?
Update Field
I am a first timer on this forum so apologies if I sound a bit dumb. I have a table called sortcodes where there is a sortcode number which I need to increment by one for each record. I have the "start number" stored in another table. I am using a stored procedure as I thought I would need to do some sort of loop. But I can't seem to achieve the required result. I thought I would be able to do it with an UPDATE table command within a loop but of course the UPDATE command just goes and updates all the matching entries in my sortcodes table without incrementing my counter.
Update A Field
i am doing a forth year project in college and i need to update column week from 1 to 2 when a certain time occurs. is this possible if so what or how do i go about doing it?
UPDATE Field
I want to number records consecutively with an UPDATE command. Can this be done my phpMyAdmin? I tried SET @a:=1; UPDATE table SET field=(@a:=@a+1) where id (condition); (usage is to 'blank out' sensitive information by storing e.g. 'sensitive1', 'sensitive2', 'sensitive3',... and so on in the field.) (yes I prefer it consecutively, the alternatives 'random' and 'id' are in my line of sight).
Update A Field
on my site i have fields in the members database for downloads and submissions. i recently cleared and restored the files but these statistics have been lost and i'd like to figure out the mysql code to restore them. i have a table of "files", with the members id as, m_id and in the "members" table with the field "downloads" and id stored as "id" i realise this is fairly simple but i just cant get my head around the sql syntax yet
Update Field In A Trigger
I'd like to set a field to the same value as primiary key(id) in a trigger, here is what I have come out: CREATE TRIGGER update_seq AFTER INSERT ON `usr` FOR EACH ROW BEGIN SET old.seq=old.id; END ; id is the primary key while seq is a field that I want it to be same as id, but this trigger does not even compile, got this error: Updating of OLD row is not allowed in trigger any idea how to do this?
Update WHERE Field Exists
is there a way , in MySQL to do this: i have a table with rows id,name,passwd,nickname Check if row with `name`='harry' exists if so, UPDATE with new data if not, INSERT a new record now i'm doing this with a php if else construct, but there must be an easier way.
Update One Field In All Rows
I have a mysql database which contains 24,000 rows. I added a field called "RECALL", which contain null values. What I'm trying to do is fill that field in all 24,000 rows with the value "N".
Can't Update A Field From VB Program
I've just setup MySQL on a Linux machine and I'm using Visual Basic in Windows to connect to it (using MySQL ODBC 3.51 driver). Everything works fine for browsing the database (used for stock control), but some parts of the VB program refuse to update the database. It was using an Access database (which worked ok) and I'm using the same SQL statement and table structure. Code:
Update Field Isn't Working?
I've created a MySQL database that has one table, that table has two fields, the table is called system, the fields are called status and message. The database is used to populate a html table on a page, that works fine. What I need to do though is have a form on a separate page (which i'm going to add http authentication to) with a simply form consisting of a text box, three radio buttons, and the usualy submit and reset buttons. I need to be able to update the database using this simple form. However this isn't for adding new records, it is purely for editing the existing record as it's to edit simple warning messages to display to the visitors when there is a problem with one of the systems. The radio buttons determine which light image is displayed (green, orange, red) and the text box for the short message. However no matter what i try it just in't updating the fields in the database, the following is the html and PHP i'm using: Code:
Can I Do An UPDATE On A Timestamp Field
I am creating a time management system whereby the administrator can go in and UPDATE an existing Timestamp field with a new time. I have the administrator input the date and time in a textbox. Any clue how he can update the field with this new data??? The table TimeTable has columns like TimeStamp, DateStamp and UserID besides Autonum. I think it should be something like... UPDATE TimeTable set TimeStamp=06/29/2005 12:45:56 PM where UserID=10 and DateStamp=06/29/2005 This however errors on me--any clues?
Update A Date Using A SUM(field)
I have been asked to come up with a way of updating a date field, so that it subtracts the total number of days. If there was only one record to add to the field then it would be a simple case of DATE_SUB(m1.date_joined, INTERVAL p1.qual_days DAY) but unfortunately, there can be more than one record in the second table, so I need something like DATE_SUB(m1.date_joined, INTERVAL SUM(p1.qualdays) DAY) but it won't work, even after I add in a GROUP BY statement at the end of the query, I get an error using GROUP BY. I have now resorted to creating a temp table to dump all the calculations in there and then doing an update using the values but I'm trying to find out if there is a better way of doing it.
Update Mysqld Db With New Field
I have 2 MySql dbs on 2 different servers. I need 2 copy the data from one field in db1 and add it into a new field ind db2. What is the easiest way to do this? Both tables have the same number of records. In db1, tableoriginal let's say there's an int field named id and a field named copyme (varchar). The second database has a table called tablenew and has a field called first_table_id that mirrors the original table's id field and I just inserted a new field called pastemehere (varchar).
Can't Update A Field From VB Program
I've just setup MySQL on a Linux machine and I'm using Visual Basic in Windows to connect to it (using MySQL ODBC 3.51 driver). Everything works fine for browsing the database (used for stock control), but some parts of the VB program refuse to update the database. It was using an Access database (which worked ok) and I'm using the same SQL statement and table structure. Here's the SQL statement that VB runs: SELECT Stock.GJGStockCode, Stock.PartName, Stock.Discontinued, Stock.UnitsInStock, Stock.UnitsOnOrder, Stock.ReorderLevel, Stock.ReorderQty, StockSuppliers.SupplierID, StockSuppliers.OrderCode, StockSuppliers.PackQty, StockSuppliers.Price, Stock.PartsToOrder FROM Stock, StockSuppliers WHERE Stock.CurrentSupplier = StockSuppliers.Position AND Stock.GJGStockCode = StockSuppliers.GJGStockCode AND ((Stock.UnitsInStock + Stock.UnitsOnOrder <= Stock.ReorderLevel) OR (Stock.PartsToOrder > 0)) ORDER BY StockSuppliers.SupplierID, Stock.GJGStockCode ....which works ok for browsing the database. If I change the Stock.PartsToOrder field in the VB program, when VB trys to update it I get the error message "Insufficient key column information for updating or refreshing". The Stock table has a primary key (GJGStockCode), and the StockSuppliers table has a primary key (UniqueID) which is an auto_increment bigint. Does anybody know what I need to do to allow VB to update the database? Or is this a VB question!?? - Although the same SQL statement works with an Access database.
Can't UPDATE A TEXT Field
I am creating an events calendar and one of the fields is a description field, which is a TEXT field. I am unable to use the UPDATE command to update this TEXT field. I have read that for MS SQL, you need to use UPDATETEXT but alas, I am using MySQL and there is no such command. I have found nothing in the MySQL documentation on how to update this TEXT field. Could someone shed some light on this, or direct me to the article in the documentation on how to update TEXT fields?
How Do I Update Part Of A String Value In A Field
I have information in a field as follows: |___col___| | AAXXAAA | I need up update all instances of the substring 'XX' in the 'col' field to 'YY', but I do not know how to structure the SET portion of my update statement to do this. Below is my update statement minus the set portion. UPDATE db.table SET col = <need help> WHERE col LIKE '%XX%';
Make A Field Automatically Update...
I'm creating a little test database called math with a table called grid, so math.grid. I have a few columns in it, first_number, second_number, sign, answer, and ID. I want to know what I can do to have the ID field automatically change when a row is deleted. So if I have 4 rows and they're numbered 1-4, that if I delete #2, it will update to become 1-3 instead of 1, 3, 4. I'm wondering if this is possible..
|
TRACKER
