Conditions In UPDATE Statement
i have some data to be updated in a specific row - WHERE id=1
data:
$min_time, $max_time, $time, $attempts
so, i would like to calculate new avg and replace min_time with new value, if new min_time is smaller then that one stored in the database.
"UPDATE mytable SET
avg=(avg*attempts+$time) DEV (attempts+1),
min_time=**here i could use help** ??IF(min_time>$min_time)$min_time ELSE min_time??
**and similar with max_time .
View Complete Forum Thread with Replies
Related Forum Messages:
Case Statement And Multiple Conditions
I have a case statement where I have multiple conditions that are translating into one variable that I am using to group my results by. My problem is that I have data that meets more than one of the conditions and I need an accurate count if the data crosses multiple categories. Here is an example of what I have: case when week(smbt.date_submitted) >=35 then 'New' when sc.Status_Code = 80 and rc.Resolution not in (10,30) then 'Resolved' when sc.Status_Code = 90 and rc.Resolution not in (10,30) then 'Closed' when rc.Resolution in (10,30) and sc.Status_Code not in (80,90) then 'Open' else 'All Other' end as status The data that I am pulling can be New and Closed, or New & Resolved or New and Open, or New and Other. Is there anyway to formulate this statement to account for that combination, but still translate into the same "status" variable while keeping each cateogry (New, Resolved, Closed, Open, Other).
View Replies !
Select Statement Using Multiple AND/OR/REGEX Conditions
I am trying to figure out the correct syntax for querying my database for strings in two columns from one table. SELECT * FROM figure_list WHERE num_fig > 0 AND (keywords REGEXP '^soldier,' OR keywords REGEXP '$,soldier' OR keywords LIKE ',soldier,' OR keywords = 'soldier'); The 'num_fig' column contains an INT from 0 - 5. I am only concerned with those greater than 0. The 'keywords' column contains a series of strings seperated by commas. Here are some examples. 1. Soldier 2. Soldier,Apples,Army of Carrots,Hickory Smoked Sausages 3. Oranges,Mangos,Soldier,Poop 4. Kiwi Soldiers for Peace,GSM Carriers 5. Rock,Paper,Soldier I am looking to find any row that 'keywords' contains 'soldier'. So the fourth example above would not count, since it is 'Kiwi Soldiers for Peace' and not 'soldier'. I am trying to search for anything that: 1. Begins with 'soldier' using: keywords REGEXP '^soldier,' 2. Ends with 'soldier' using: OR keywords REGEXP '$,soldier' 3. Contains 'soldier' using: OR keywords LIKE ',soldier,' 4. Is 'soldier' using: OR keywords = 'soldier' If any of those are true then I want to return them. The above SELECT statement gives me a much smaller list than I know is true. I know there is something wrong with my syntax but I haven't had any luck finding a solution on tutorials/guides online.
View Replies !
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 .......
View Replies !
Multiple UPDATE, Same Column, Different Values, Different WHERE Conditions
I've found plenty of info around about updating multiple rows with the same value using "WHERE columname IN", and I've got that down. But, I'm needing to UPDATE a column in multiple rows with a different value for each WHERE condition. My updates are being done as individual queries like this: SQL Code: UPDATE tablename SET widget='zamu-xxx' WHERE widget='zamu'; UPDATE tablename SET widget='flabu-yyy' WHERE widget='flabu'; Is there any way to do something like this in a single query?
View Replies !
Join Conditions And WHERE Clause Conditions
I'm trying to get a clearer understanding of the difference between Join conditions and the more standard WHERE-clause conditions. I only discovered Join conditions a few months ago, and I really like them because they allow me to separate the conditions I wanted for joining tables out from the conditions I wanted to use to restrict what kinds of rows I wanted. This gives me queries which are much more readable. Some of the queries I have written now involve joining as many as 6 tables together. In some of these, I only want to join as subset of one table to the other and one or two queries involve a NOT EXISTS subquery which I am rewriting as a LEFT JOIN (because, as I understand it, in general, MySQL is better at joins than subqueries, and I dont have the data yet to work with to see some performance statistics and compare them). Because of this, there have been a few times where the natural course of action has appeared to be to put a condition that isn't equating two columns in different tables into my join condition. And MySQL doesn't appear to complain. For example, if I have a table (Table1) whose rows refer to "jobs". A particular job can be referred to by many rows in Table1, but I only want the jobs which are active (Jobs.active = 'y'). Because I only want to link Table1 to active Jobs, I CAN put this condition in the join condition, like so: FROM Table1 JOIN Jobs ON Table1.jobID = Jobs.jobID AND Jobs.active = 'y' My question is, what are the differences between putting conditions in the FROM clause and the WHERE clause? Are there any? Does MySQL treat them exactly the same? I am asking, because I believe if I have a good understanding of the differences (if there are any), I will be more able to make a better judgement on which clause the condition should be put into. ================================================================= On the other example, of the subquery, if I have a sort of messaging system that sends and receives messages with outside agents. For statistical purposes, when I broadcast a message to multiple people, I want to be able to count the number of people who responded to it. So, when I receive a message from someone, I need to relate it to the last message I sent out to that person (and count it), BUT if the person sends the system more messages without having received one from the system, I dont want them to be counted. BroadcastResponses is the table that records these statistics. A row is inserted into it when a broadcast is sent out. ReceivedMsgs is the table that records messages I receive from users. My first atmysqlt went like this: ....
View Replies !
SQL-Update-Statement
I have to update all UA-fields in the table idrz_kennzahl with primary key idrz, where the same idrz in the table per_verwaltung with the field Amt=12 is.I designed following statement but it doesn't work: update idrz_kennzahl set UA=1234 where idrz_kennzahl.idrz =(SELECT per_verwaltung.idrz from per_verwaltung where per_verwaltung.Amt=12
View Replies !
Update With If Statement
I'm trying a statement which I cannot figure out what I'm doing wrong on. I've tried every combination that I can think of and stared myself blue with no success. update cdr set Cost=if(mod(BillSec,6)=0,int(BillSec/6),int(BillSec/6)+1) The idea is to update Cost with a value depending on the result of a formula. It's an empty field which gets a computed value from other fields. cdr is the table.
View Replies !
Update Statement
I am currently updating a table and increasing a column value by 1, the problem is that he don't increase 1 but it does increase the double of the value specified. Why does it performs the update like this? And how can i solve this problem? SQL Statement Syntax used: UPDATE table_name SET column_name=column_name+1 WHERE id=1 Version of MySQL: 4.1.7 for Windows installed in Windows 2003 Server.
View Replies !
Long Old UPDATE Statement
I have the following SQL which gives me the row Im after SELECT * FROM staff, staffemails WHERE staff.users_username='$user' and staff.staff_id = staffemails.staff_id thing is I now want to perform an update on the result of this query. Ive tried UPDATE staffemails SET is_registered ='0' WHERE staff.users_username = '$user' and staffemails.staff_id = staff.staff_id .
View Replies !
Update Statement Not Working
Why does this simple update statement not work with a mysql version 3.23.49, it does work when using 4.0.24..... ? UPDATE listitem,vjbase,list SET listitem.showsec = 7 WHERE listitem.id_vjbase = vjbase.vjbase_id AND listitem.id_vjbase = 3 AND vjbase.id_viduser = 61000 AND listitem.id_list = list.list_id AND listitem.id_list = 1 AND list.id_viduser = 61000
View Replies !
UPDATE Statement Doesn't Work
TABLE - abc ID NAME ___________ 1 hello 12 abc UPDATE `abc` SET `ID`='5' AND `NAME`='hello' WHERE `ID`='1' AND `NAME`='hello' wherenver I try to update this table using above sql statement, it doesn't work. I just get "Row match:1 Changed:0" message.
View Replies !
Very Slow Update Statement
I am having an issue with an UPDATE statement that takes a very long time. I am using 1 table in a schema to update another table in another schema. Below are the create statements and the update statment I am using. Table and column names have been changed to protect the innocent :) Code:
View Replies !
Special UPDATE Statement
UPDATE some_table st SET some_page_no=((select count(*) FROM some_table WHERE some_table_id<st.some_table_id and some_id=st.some_id and some_type='OK') div 10)+1 WHERE some_id=345 and some_type='OK' I want to use the above to make a fast page index (10 on each page). I had something similar in mssql and it worked, but in myssql I get this error: "You can't specify target table 'some_table' for update in FROM clause" What have I done wrong or can the above simply not be done?
View Replies !
UPDATE / JOIN STATEMENT
i have problems after updating mysql from 4.x to 5.0.19. This statement should update about 3000 rows but it only does 15 but there are definitely another 2000 rows with an older date and matching nr! UPDATE tab1,tab2 SET tab2.newdate = NOW() WHERE tab1.nr=tab2.nr; I tried the variation with inner joining tab1 as well. same result. Does anyone has an idea solving this problem? Perhaps it´s a bug?
View Replies !
Select + Update In One Statement
I have these statements. $selectdata = "SELECT * FROM imagetrack WHERE fimsid = ".$_GET["fimsid"]." AND name = '".$_GET["service"] . "'"; $query = mysql_query($selectdata) or die(mysql_error()); $update = "UPDATE imagetrack SET seen = seen+1 WHERE fimsid = ".$_GET["fimsid"]." AND name = '".$_GET["service"] . "'"; Basically i was wondering if there was a statement where i can select everything and update the seen in one statement.
View Replies !
Update Statement Problem? Plz Help.
I've a simple doubt of whether can or can't i update two tables by using joins as below. the below update statement matched 2 rows one each for two tables but didn't update. mysql> update audio join pcr_info on audio.audio_sid=pcr_info.pcr_sid set audio. audio_pid=4896,audio.audio_prgm_num=3,audio.audio_type="MPEGLayer1",audio.audio_ sr=44.1,audio.audio_mode="1_0",audio.audio_bitrate=320,pcr_info.pcr_pid=49 where pcr_info.pcr_prgm_num=3 and pcr_info.pcr_sid="2501" and audio.audio_pid=4896 an d pcr_info.pcr_pid=49; Query OK, 0 rows affected (0.00 sec) Rows matched: 2 Changed: 0 Warnings: 0
View Replies !
DELETE,UPDATE Or Other Statement In WHERE
I would like to let my admins write WHERE statements. . In panel where you can see all users of my site, admin should have input where he can put some filters in WHERE statment. E.G. standard query is SELECT login FROM USERS . and admin would write age > 18 and male = 'f' . php script would conact it and it would execute SELECT login FROM users WHERE age > 18 and male = 'f' I have question about security of this solution. Is possible to put in WHERE statement sobe dangeurous statemetns like DELETE of UPDATE?
View Replies !
Conditional Update Statement
I have a table with a field of ID numbers. I want to update this field and replace the ID numbers. For example I want every record that currently has the ID number 2,5,6,or 9 to be replaced with a 1. Every record with 1 or 4 to be replaced with 2 ... I can't seem to form the update statement to do this. I can do them one at a time but this won't work because if I change 2,5,6, and 9 to 1 then when I run then update for 1 and 4 it will change them all. Does this make any sense? I want some way to be able to use a condition...if id=2 or 4 or 6 set it to 1 and if id= 1 or 4 set it to 2. My failed attempt: mysql> update client_software set software_id=1 WHERE (software_id=2 or software_id=5 or software_id=6 or software_id=9) AND software_id=2 WHERE (software_id=3 or software_id=7 or software_id=8) AND software_id=3 WHERE (software_id=1 or software_id=4);
View Replies !
Insert Or Update In One Statement
I sometimes find myself writing PHP code that checks for a value in a table and updates it with a new value if it exists, or insert it into the table if it doesnt. Is there any way to do this with one sql statement?
View Replies !
Conditional Update Statement
MySQL Code: UPDATE   tournamentsSET   tournament_player1 = IF (tournament_player1 = 5, 1, 0),    tournament_player2 = IF (tournament_player2 = 5, 1, 0),   tournament_player3 = IF (tournament_player3 = 5, 1, 0),   tournament_player4 = IF (tournament_player4 = 5, 1, 0) WHERE  tournament_time = 1 AND tournament_game = 1 ....
View Replies !
Convert SELECT Statement To UPDATE
How do I convert the following SELECT statement to UPDATE statement? If I'm not mistaken, UPDATE statement could not involve more than one table. SELECT * FROM F0116 LEFT JOIN F0101 ON F0116.ALAN8=F0101.ABAN8 WHERE F0116.ALCTR='' AND F0101.ABMCU='1'
View Replies !
Update Statement Not Working On Server
this works on my machine but not where my site is hosted. They do use an older version of mysql could that be problem update inv, newprice set inv.cost = newprice.price where inv.itemcode = newprice.itemcode works fine on my machine her ebut on server i get error..
View Replies !
Update Statement :: Affected Rows: 0
UPDATE `zipLocations` SET `z_zipCode` = '06001' WHERE `z_zipCode` =6001 LIMIT 1 ; Field Type Attributes Null Default Extra Action z_zipCode int(5) No 0 z_cityName varchar(50) No s_stateAbbr char(2) No z_countyName varchar(50) No z_areaCodes varchar(50) No I even went through phpmyadmin found the row clicked on the edit button for that row and updated it. and it still doesn't update.. im' thinking it has to do with the data type being INT... Why doesn't it update it ?.. It says it was successful. Affected rows: 0 (Query took 0.0002 sec)
View Replies !
CASE Statement In Update Command
I'm trying to update one table (tbl_usage) based on information from another table (tbl_subscriptions) joined by the userID. The usage table tracks the number of times a user hits a page, and decrements the count each time. When the user has a zero count, they are redirected to a warning page. Each week the count is reset depending on what type of license they have (licenseType) and how many of those licenses they've bought (numLicenses). A group license (G) gives them 100 hits, and an individual license (I) gives them 5 hits. I'm trying to use a case statement to perform this count reset operation, but I can't seem to get it to work. The following case statement works just fine: Code:
View Replies !
Convert A Nested Update Statement
i have a nested update statement in my sql server as follows UPDATE DIM_EVENT . SET INVOICE_AMOUNT = (SELECT SUM(INV.INVOICE_AMOUNT_TOTAL) FROM INVOICE INV WHERE INV.INVOICE_REFERENCE_NUMBER = DIM_EVENT.OBJECT_ID AND INV.INVOICE_AMOUNT_TOTAL IS NOT NULL) WHERE DIM_EVENT.EVENT_TYPE_CODE IN ('OFULN', 'OAUTH', 'CRDRL'); what is the equivalent in MySQL.
View Replies !
Update Statement :: Data From One Table To Other
I have two tables, and I want to take data from one table, and overwrite it onto the second. Unfortunately, my SQL is pretty weak. The first table has a mediumtext column and a id (primary key) column. The second has an id (the same as the first), a number, and a mediumtext column. I want to take the contents of the mediumtext field with the highest number and replace the contents of the field in the first column with it with the same id. My instinct is to do it this way: update forum1post p set p.pagetext = (select query1.message from (select pe.message, max(pe.editnum) from forum1post_edits pe group by p.postid) query1) where p.pagetext = 'Why did you all flame my topic. Screw your ****ing forums.' and p.postid = pe.postid However, that does not work, as pe is not recognized.
View Replies !
Help With Simple SQL Statement To Update Zipcodes
I have a varchar(5) field in a database called 'zip_code'. I imported some zipcodes from an Excel spreadsheet, but the spreadsheet had truncated any zipcode that started with a "0" and removed the zero, so the correct zipcode of "08993" was incorrectly placed into the record as "8993". Would someone help me write a SQL statement to run on the field zip_code that would look at the data, if it was four characters long, add a "0" to the start and keep the remaining four characters in place?
View Replies !
QUERY One Table And Update Another In ONE Statement?
ZIP_CODE: zip_code latitude longitude customers: customer_id ... zip_code latitude longitude The lat and long in the customers table is blank. I want to lookup customers' zip code in the ZIP_CODE, get the related lat & long and update the customers table with that information. The result being that after this query is run, the customers table now has lat & long data.
View Replies !
MySql Update Or Insert Statement To Modify A Word In All Caps
I have a field in my database called Subtopic. In this field I have a word LITERATURE in all caps. I need to change the word LITERATURE to LITERATURE followed by a colon then an empty space. Can someone give me the MySql statement I can paste into phpMyAdmin to make this change? Note: The Subtopic field contains the word "literature" but not in all caps because the Subtopic field is part the database that is an encyclopedia. Therefore, I want to change ONLY the word LITERATURE if the word LITERATURE is in all caps.
View Replies !
WHERE Conditions
I'm trying to optimize a search and I'm wondering if anybody knew whether the order of the conditions in the WHERE clause actually matters. Is it parsed from left to right? If so, then should I put the conditions likely to eliminate the most records first, followed by those that will eliminate the least? Also - I need to join several tables for this search (1 master table that contains user info and about 7 secondary tables that include additional information on users) and I am trying to decide whether I should do the joins one at a time and eliminate users in a series of steps (to prevent getting an overly large table), or if I should evaluate and combine everything in a single statement. The problem is I don't really know what the parsing order of the SELECT statement is.
View Replies !
LIKE Conditions
Basically I have a table, with a column which holds a list of related tags separated by spaces. The user can search for a row using a key word that will search this column (and the column named header too) and return if the keyword appears in the tags (or heading)... I tried to achieve this by using the following SQL but it actually returns every entry in the database... I have never used "LIKE" before so if someone could give me a slap and say I am doing something stupid that would be awesome . SELECT * FROM VIDEOS WHERE VIDEO_HEADER LIKE '%$seachText%' OR VIDEO_TAGS LIKE '%$searchText%'
View Replies !
IF Conditions
I have the following code: IF (SELECT id WHERE base_narrative_id = 1) THEN UPDATE `user_narrative` SET `new_message` = 'You have new message' WHERE base_narrative_id = '1' AND user_id != '1' ELSE INSERT INTO user_narrative(id, base_narrative, user_id, new_message) VALUES('', '4', '2', 'new message') END IF; But it doesnt work. I think the problem is with my if statement. How should it look? All i want to do is find out if there is an entry for the specified base_narrative_id, and if there is update it, if there's not create one.
View Replies !
MySQL 5.0 And OR Conditions
I have heard that version 5.0 of MySQL has performance gains for "OR" conditions. Can anyone confirm this? If so, how significant are the performance gains over 4.1? For instance, I am referring to something like: SELECT * FROM some_table WHERE this_thing = 'something' OR that_thing = 'whatever';
View Replies !
Select With Conditions?
Say i have this table: Quote: name type A 1 B 1 C 2 D 2 In one query, i would like to select 1 name with type 1, and another name with type 2. so the result is it gives me at least one of each type.
View Replies !
Sums For Two Different Conditions
I want to combine the following 2 queries into 1 query where it would give me 2 results (proflad and proftot) but don't know how select sum(return-inv)as proflad from allbets where com="lad" select sum(return-inv)as proftot from allbets where com="tot"
View Replies !
Order By With Conditions
I have an items database that has Price and SPrice fields with a status of OnSale or Online. Obviously when Online the normal Price is used and when OnSale the SPrice is used. With that in mind here is what I am having trouble doing. I want to sort items by sprice only if the item has an SPrice and is OnSale after that sort by Price. Is this possible without doing 2 queries or doing a sort() function in PHP once I gather the data? I am using MySQL 4.3. I have tried to upgrade to 5.1 but it seems to always corrupt my data and I don't think doing a "IF" statement works in 4.3. I tried doing an DECLARE NewOrder VARCHAR(12); IF sprice > 0 and (Status = "Sale" or Status = "Clearance") THEN SET NewOrder = 'sprice,price' ELSE SET NewOrder = 'price' END IF This doesn't appear to work in 4.3 or I am doing it wrong. For that matter I don't even know if that would work.
View Replies !
Search On Several Conditions
I want to retreive data from a table on several conditions in order; for example; All i am trying to do is select all records on the following condition; User_id = variable AND (Profile = "variable" OR Profile = "Both") ORDER by hits in DESC I came up with this statement but no matter what i do i cannot get it to produce the result above "SELECT * FROM links WHERE user_id='$user_id' AND (profile='$mycurrentprofile' OR profile='Both') GROUP BY total_hits DESC" Can anyone offer me any help please, would really appreciate this as its doing my head in. BTW, the following statement seems to work so not sure whats going on ? Only thing i can think is that its screwing up if total_hits above is equal 0 ? "SELECT * FROM links WHERE folder_id='$activefolder' AND (profile='$mycurrentprofile' OR profile='Both') GROUP BY title ASC"
View Replies !
Need Help With Multiple WHERE Conditions...
i'm wondering if anyone knows how to have multiple WHERE conditions...i'm using php/mysql as my programing languages... example --> SELECT * FROM table WHERE rid=$ride && uid=$userid && accepted=Ɔ' I need help selecting rows where all three of the conditions above are met...anyone knows how to do this?
View Replies !
2 Of 5 Conditions Match
I need my query to match at least 2 of 5 given conditions. Example: I have conditions content REGEXP 'A' content REGEXP 'B' content REGEXP 'C' content REGEXP 'D' content REGEXP 'E' I need database to return this entry only if at least 2 conditons are true.
View Replies !
SELECT WHERE - TWO Conditions?
PHP Code: $result = mysql_query("SELECT * FROM orders WHERE orderid = '$orderID' WHERE verified = 0"); I only want it to display the result if the order ID matches and verified is
View Replies !
Using WHERE And Multiple Conditions
$query = "UPDATE Sales SET show = '1' WHERE ((sale_type != 'one_day' AND month_end = '$target_month') OR (sale_type != 'one_day' AND month_end = '$this_month' AND day_end < '$today') OR (sale_type = 'one_day' AND month_start = '$target_month') OR (sale_type = 'one_day' AND month_start = '$this_month' AND day_start < '$today'))";
View Replies !
|
TRACKER
