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 Complete Forum Thread with Replies
Related Forum Messages:
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 !
Conditional Statement In SQL ?
Is there way to have a sort of conditional statement in MySQL ? I have an image library (images, galleries) and an image can belong to multiple galleries. So I have also a rel_image_gallery table. When I transfer a bunch of images from one gallery to another all at once, there is the possibility that an image is already in the destination gallery. So I need to check something like "if image already in gallery, do nothing, otherwise add it". I can do that in PHP but if I could do it directly in SQL, that would be more efficient I think.
View Replies !
Conditional JOIN Statement
I need some help with an SQL statement. Here is the situation: tblvendors - holds all information for clients tbljobtickets - holds all job tickets for vendor jobs these jobs have a status - open or closed This statement successfully pulls a recordset of all the vendor information I need joining the jobticket table to the vendors table so I can get a count on the number of open jobs each vendor has, but I want to throw another conditional in the mix only to display jobs that are open (open status = 1) ...
View Replies !
Conditional Update
Another baseball question: I have a table of the voting history for the Hall of Fame. This will give me a list of everyone inducted: SELECT playerID, yearID, inducted FROM HallOfFame WHERE inducted = 'Y' I have a Master file with records for each playerID where I have added the hofYear field. I want to update this field in "Master" with the yearID field from "HallOfFame" for the records selected above. How would I do that?
View Replies !
Conditional INSERT/UPDATE
This is my first time using PHP or MySQL. My program is grabbing values from a leaderboard and then inserting them into a MySQL table so that I can track a history. I have the database setup with (essentially) 2 tables. One to store all player data (TABLE_A Name, Class, Race, Clan, Points; where Name is a UNIQUE KEY), and another to store the history (TABLE_B Name, Points, Date). Currently I know that my script is inefficient, I am performing too many queries and it is taking a long time to update. What I do now: INSERT INTO TABLE_A stuff; Just letting the unique name keep from inserting extra data. IF( SELECT Points FROM TABLE_A WHERE Name='name_im_on' < Points) { // Seeing if a new entry needs to be put into the history table INSERT INTO TABLE_B stuff; UPDATE TABLE_A Points } So I end up performing between 2-4 queries for each player, and I am updating ~100,000 players. Is there a good way to combine these queries into one? Is there a good way to update multiple players in one query (I can do up to 50 at a time with the way I am scraping the data)? Would combining these 4 queries into a stored procedure speed things up at all (I am calling each one with it's own mysql_query from php)? I have been reading as much as I can and I am at a loss. One thing I will try doing next is making the INSERT's delayed to see if that will speed it up at all, and also making them low_priority so other things can use the database while I am updating.
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 !
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 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 !
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 !
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 !
Conditional Join
I have made a basic left join statement say "Select t.*, p.usenet_id from usenet_group_1 u left join user_table t on u.id = t.usenet_id; but only want the recordset to return values from user_table if field in user_table have a certain value ( if usenet_group = 5 ) - can this be done within the above sql statement or do i have to make 2 seperate statements?
View Replies !
Conditional Revoke
Did this: revoke all on D_name_01.* from trial_user_01; and got this response: ERROR 1141 at line 4: There is no such grant defined for user 'trial_user_01' on host '%' Is is possible to issue a conditional revoke statement so that the revoke is: performed only if applicable or NOT performed if not applicable
View Replies !
Conditional Insert
How do you write a query that inserts data into a table only if the entry does not already exist or a value is different. I have data from a sql server database that is updating a MySql table Expand|Select|Wrap|Line Numbers
View Replies !
Conditional REPLACE
suppose that there is a text like following (in datasore table) Quote: "cookiedomain";s:11:"devshed.com" i can change it using following query UPDATE datastore SET data = REPLACE(data, '"cookiedomain";s:11:"devshed.com"', '"cookiedomain";s:0:""') but there are somethings i do not know between quotes so i want create a query like "if there are somethings between quotes" namely "in between quotes are blank"
View Replies !
Conditional SELECT
I have a problem which I am not sure or it is even possible to fix with sql statements. So here it goes. SITUATION --- I have a table with 3 columns (PID, LANGUAGE, TEXT). PID+LANGUAGE is PRIMARY KEY. In this way I can have unlimited languages next to each other in one table with one correspending ID. Depending on the language chosen, I want to retrieve the text. No so hard. SELECT TEXT FROM TABLE WHERE LANGUAGE = 'spanish'. PROBLEM --- NOT all languages are presented. So I want to fall back to English if Spanish (e.g.) is not presented in the table. Of course I could choose for SELECTING english AND the language I want, and determine with PHP or I have data. But I am talking about 10k+ rows, so I dont want to retrieve everything double. Just only when I really need to
View Replies !
Conditional WHERE Clause?
I'm trying to do something which should be fairly simple but can't figure out the correct syntax. It's pretty much equivalent to: SELECT * FROM table WHERE thiscolumn = 'value1' AND 'value2' Is there a way to do this, i.e. concatenate two values from the same column in a WHERE clause?
View Replies !
Using The LIKE Conditional Twice In A Query
I can't get my head around this query and i would be greatful for any help thrown my way. Its part of my search feature on one of my sites. I have the variable $keyword which is a word or phrase the customer enters trying to find a product on the site. I need the query to search 2 fields that could contain the keyword (ie they could enter a product code or part of the products name/title). The following query works:
View Replies !
Conditional Delete
I have the following statement to remove entries from a number of tables matching a common key: DELETE items, group_user_role,groups FROM items AS i, group_user_role AS gur, groups AS g WHERE g.group_id=gur.group_id AND g.group_id=i.group_id AND g.group_id='5'; My problem is, there is not always an i.group_id value and when there isn't the condition isn't currently met. How do I make the statement work whether or not i.group_id values exist?
View Replies !
Conditional ORDER BY
I created the following table: CREATE TABLE `test_projects` ( `name` VARCHAR( 30 ) NOT NULL , `startdate` DATE NOT NULL , `enddate` DATE NOT NULL , `complete` BOOL DEFAULT 'false' NOT NULL ) TYPE = MYISAM How can I retrieve items sorted by complete, then startdate if not complete, and enddate if complete is true?
View Replies !
Inner Join Conditional
So I have two tables set up in a normalized fashion. They are users (user_id, last_name, first_name) and orgs (org_id, name, comments, assigned_to). The assigned_to field of orgs "points" to the user_id field of users. What I am wanting to do is simple enough. When I query the organization I do an inner join to grab the org's contact info SELECT org_id, name, comments, last_name, first_name FROM orgs, users WHERE assigned_to=user_id ORDER BY name ASC However, not all orgs have a user assigned to them, so the assigned_to field of those orgs is left as NULL. The above query will only pull orgs that have users assigned to them, but I also want to pull orgs that do not have users. Here's what I tried SELECT org_id, name, comments, last_name, first_name FROM orgs, users WHERE assigned_to=user_id OR assigned_to=NULL ORDER BY name ASC The server still only returns orgs with assigned_to that are not null. I could make two queries and do a merge sort algorithm on them to organize them back into alphabetical order, but I'd rather avoid that, and I think it's cheating anyway. How can I restructure my query to accomplish this?
View Replies !
Conditional Joins
This is similar to a previous thread of mine: http://www.sitepoint.com/forums/showthread.php?t=375169 I have two fields in my user table, 'lastentryid', and 'lastprivateentryid'. What I need to do is join the entry table using that entryid. Unfortunately, the results are skewed if their last entry is private, because lastentryid won't contain it. I am running into this problem in a few places, and I'd really like to solve it without using subqueries. I might even accept better organization on the db level. Here is my query as it is now SELECT user.userid, user.options, user.displaygroupid, user.usergroupid, user.username, ugroup.opentag as opentaga, ugroup.closetag as closetaga, dgroup.opentag as opentagb, dgroup.closetag as closetagb, entry.title, entry.dateline as entrydateline FROM vb_user as user LEFT JOIN vb_usergroup as ugroup ON(user.usergroupid = ugroup.usergroupid) LEFT JOIN vb_usergroup as dgroup ON(user.displaygroupid = dgroup.usergroupid) LEFT JOIN vb_blog_entry as entry on (entry.entryid = user.lastentryid) WHERE entry.dateline > $cutoff ORDER BY user.username ASC Here is what I want... but this doesn't work. SELECT user.userid, user.options, user.displaygroupid, user.usergroupid, user.username, ugroup.opentag as opentaga, ugroup.closetag as closetaga, dgroup.opentag as opentagb, dgroup.closetag as closetagb, entry.title, entry.dateline as entrydateline, if ( user.lastentryid > user.lastprivateentryid, user.lastentryid, user.lastprivateentryid ) as last_entryid FROM vb_user as user LEFT JOIN vb_usergroup as ugroup ON(user.usergroupid = ugroup.usergroupid) LEFT JOIN vb_usergroup as dgroup ON(user.displaygroupid = dgroup.usergroupid) LEFT JOIN vb_blog_entry as entry on (entry.entryid = last_entryid) WHERE entry.dateline > $cutoff ORDER BY user.username ASC
View Replies !
Conditional Statments?
Before inserting a new product for a supplier I'm trying to do two checks: 1. If product already exists for current supplier (product.supplier_id) then update the existing product. 2. If product already exists with NULL supplier_id (i.e. product with no supplier) then update that product using current supplier. 3. Else insert new product. Can I do that effiecently within mysql/php without running multiple mysql_query statments?
View Replies !
Conditional Selection
I have a table that contains some words and their translation, in various languages. I would like to select the translations for a particular language but use the English word in case their are no translations. I know how I could do it with PHP, but ideally I would like to do it in SQL only. I'm not sure it's clear at all, so here's an example: ID, translation, language 'BACKGROUND', 'Background', 'eng' 'INTRODUCTION', 'Introduction', 'eng' 'BACKGROUND', 'Contexte', 'fra' I would like to retrieve the French translations whenever they exist. In this case, only "BACKGROUND" has been translated, so I would need "INTRODUCTION" in English. The SELECT result should thus be: 'INTRODUCTION', 'Introduction', 'eng' 'BACKGROUND', 'Contexte', 'fra'
View Replies !
Conditional Union
My objective is to gather all the type groups for a mailing list. I also would like to add to the list an 'Undefined' item for all the users that are ungrouped: 1. I'm trying to make a conditional union, where a union will occur only if the previous condition is true (if any null typeIDs are found) 2. I would like to make one query only [to use with a php function wich only allows single queries] So this is a 'sketch' of my query: SELECT typeID, name FROM pa_users_broadcast_types UNION IF((SELECT COUNT(*) FROM pa_users_broadcast WHERE ISNULL(typeID))>0) SELECT (Ɔ') typeID, ('Undefined') name;
View Replies !
|
TRACKER
