Select And Update Statement
Is it possible to combine a select and update statement in mysql? If
so, what is the syntax.
View Complete Forum Thread with Replies
Related Forum Messages:
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 !
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 !
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 !
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 !
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 !
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 !
Select Statement Question (nested Select?)
I have a DB containing 3 fields fullname, inext, and outext. I need to see all the records that have a duplicate entry in inext. I know I can do a distinct query on the inext column but that only gives me the unique ones I need all the different duplicate records.
View Replies !
Combining SELECT Statements Into One SELECT Statement.
I want to take the results from: SELECT name.empnumber, name.firstname, name.lastname FROM name INNER JOIN authuser ON name.empnumber = authuser.uname AND authuser.team = 'PHQ' ORDER BY name.lastname, name.firstname; and the results from: SELECT name.empnumber, name.firstname, name.lastname FROM name INNER JOIN crew_attendance_6QJ ON name.empnumber = crew_attendance_6QJ.empno ORDER BY name.lastname, name.firstname; And combine them into one query that outputs all of the results both queries would output. Then order those results. So far I have come up with:
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 !
Select Statement!
I have a simple query that is similar to this one: SELECT distinct b.username from permissions a, users b, links c where a.user_id = b.id and c.information = 14; In this case, it selects the all users details from table 'user' that are permissioned to access a certain information with id = 14. The problem is that I need to select the users that are not permissioned but I have sql v3.23 and I canot use union. In other words, I need to do a select b.username from users b where b.username not in "the result" of the last select.
View Replies !
One Select Statement
I have a table with the fields id | titleid | catid Each title can have more than 1 catid so it's possible for the fields to look like this: id | titleid | catid 1 1 1 2 1 2 3 2 1 Now I want to select any rows where titleid matched more than one catid. In this example I would like to select titleid 1 because it is in catid 1 and catid 2 but I can't seem to figure out a statement that properly says "give me any titleid that has a catid of 1 AND a catid of 2"
View Replies !
SELECT Statement With OR
I need some help with this select statement: SELECT * FROM gallery WHERE type = 'skate' AND park = 'Both' OR park = 'Park 1' I just need it to look at one table and grab all the rows that have a type of 'skate' and have a park value of either 'both' or 'milwaukee'. I think the OR is throwing it off.
View Replies !
Regarding Select Statement
I am retrieving data from two tables using the UNION statement and i am thinking of having a field in my resultset(this field is not found in the database) to distinguish the data from two tables.Is it possible to set a fixed value for a particular field using the select statement?For example, a field value of 'buy' and 'sell' values are used to distinguish between the two tables.
View Replies !
Select And A Max In The One Statement
Can you combine a select and a max in the one select statement? Eg,. I want to select like select ids from table_name where ids > $idsvariable... order by ids.... but I also want to grab the highest ids number so I can put $idsvariable (max) into a table so next time it selects only the ids higher than the last time the select was run! So if ids were: 2 5 6 9 12 14 15 then I'd like to get 15 as the max as well... any examples.
View Replies !
Select Nav Statement
I have a nav table in my database that grabs information to put into the navi menu, this is dependant on user access etc... i have an item in my table that is in zone "members" and it is marked for permission Z. Although when i run the query it dont pick it up.... these are the queries that ive tried... SELECT * FROM acwe_nav WHERE privs LIKE '%A%B%C%D%E%F%X%Y%Z%' AND zone='members' ORDER BY weight; SELECT * FROM acwe_nav WHERE privs LIKE '%ABCDEFXYZ%' AND zone='members' ORDER BY weight;
View Replies !
Else If In Select Statement
i have a bunch of records that i want to pull on the basis of eg SELECT lat, lng, html, label, icontype FROM ade WHERE icontype='" . $_POST['name'] . "'"; but i'd like to make the first query and also another on an "if else" basis to cover various bases if i post a query where the 'name' isnt an icontype, i want it to look in the "section" field and pull those records matching a certain value ie all records that have a value of "5" is it possible to do something like this SELECT lat, lng, html, label, icontype FROM ade where icontype='" . $_POST['name'] . "' ELSE IF section='" . $_POST['name'] . "' ELSE IF catid='" . $_POST['name'] . "'";
View Replies !
Is There Something Like A SELECT ALL BUT Statement?
Usually the tables I have to handel have a high number of columns. Additionally, columns are deleted and added dynamically. Most calls made on these tables are select statements: Consider two tables t1 and t2. A natural join results in the columns a,b,c,d,e,f,g,h,z. A usual select statement is the following: SELECT a,b,c,d,e,f,g,SUM(z) FROM t1 NATURAL JOIN t2 GROUP BY a,b,c,d,e,f,g This way the column h could be "eliminated". As you can see the select and the group by clause can become fairly long. What I am looking for is a clever way to say "Group by everything but h and z". Is that possible?
View Replies !
Select Statement MIN()
SELECT * FROM Distance, LorryDetails WHERE cavan = (Select min (cavan) From Distance where cavan>0); here is the results i am getting source ,Cavan, Athlone, Cork, Galway ,LorryID, LorryReg, Position Athlone 90 0 180 80 3 02-CN-8001 Cork Athlone 90 0 180 80 7 00-CN-1000 Cavan Athlone 90 0 180 80 10 02-G-8001 Galway What i want to try and do is compare the Values with the Column names and get the shortest path. for example when you compare the position cort with the column cork it is 180, cavan is 90, and galway is 80. Galway is the nearest to athlone so i want to narrow down the results to just give me back the row where the position is equal to Galway. Can anyone help me with this.
View Replies !
Select Statement Within Another?
Tag_id::Img_id::Tag::counter 001::1::Tree::70 002::2::Tree::50 003::3::Pond::30 004::4::Flower::20 005::5::pink::60 lets say the above is my table. this is my current query: SELECT tag, max(counter) FROM tags GROUP BY tag DESC limit 3 this is the result I get: tree 70 pond 30 pink 60 here is can someone pls tell me a way to get the total of the counter of all the occurrences of a particular tag(than the max(counter) ). so that it'l be tree 120 pond 30 pink 60
View Replies !
SELECT With An AS Statement
I'm using a LEFT JOIN to mash two tables together. It works fine. I'm feeding this result into PHP's mysql_fetch_assoc. mysql_fetch_assoc doesn't handle columns with duplicate names very well, and these two tables do have duplicate names. So, I am using AS to assign an alias to these duplicate column names. But, these tables have about 20 fields each, and I can't figure out how to use my SELECT statement so that it selects all of the fields from tables (SELECT * FROM . . . ) but also assigns an alias to a specific field.
View Replies !
SELECT MIN, MAX And IF Statement
I have a few entries with a start timestamp, and an end timestamp. What I'd like to know is the earliest start date, and the latest end date, to define the complete time span of the selected entries. Example: id, date_start, date_end: 1, 0000-00-00 00:00:00, 2009-02-07 12:00:00 2, 2009-01-01 12:00:00, 2009-01-05 12:00:00 3, 0000-00-00 00:00:00, 0000-00-00 00:00:00 As you can see, it's possible that a start_date and/or end_date aren't inserted. If I now select the MIN(start_date) and the MAX(end_date) from the above entries: SELECT MIN(start_date) AS start, MAX(end_date) AS end FROM entries WHERE ... The result is: start = 0000-00-00 00:00:00 end = 2009-02-07 12:00:00 What I'd like to return, is the lowest possible date_start, in case a date_start has been entered. In the example above, this would be 2009-01-01 12:00:00, because that's the only date_start that isn't NULL. I tried this: SELECT IF(start_date>0, MIN(start_date), NULL), etc...
View Replies !
SELECT With IF Statement(?)
# Table structure for table `db_emails` # CREATE TABLE `db_emails` ( `id` int(10) NOT NULL auto_increment, `user_id` int(10) NOT NULL default '0', `email` varchar(255) NOT NULL default '', `main` int(1) NOT NULL default '0', `list` int(2) NOT NULL default '0', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ; # # Dumping data for table `db_emails` # INSERT INTO `db_emails` VALUES (1, 1, 'a.a@a.se', 1, 1); INSERT INTO `db_emails` VALUES (2, 1, 'aa.aa@a.se', 0, 2); INSERT INTO `db_emails` VALUES (3, 2, 'b.b@b.se', 0, 1); INSERT INTO `db_emails` VALUES (4, 2, 'bb.bb@b.se', 0, 2) So, now to my question. How can i do a SELECT where i want all email adresses BUT only the main if main=1 and all if main=0. The result for this dataset should be: a.a@a.se b.b@b.se bb.bb@b.se
View Replies !
Select Statement
How can I do a select statement for this problem: If a in ('1', '6', '11', '16', '21', '26', '31', '36', '41', '46', '51', '56') then '1' as ‘stemp’ If a in ('2', '7', '12', '17', '22', '27', '32', '37', '42', '47', '52', '57') then '3' as ‘stemp’ If a in ('3', '8', '13', '18', '23', '28', '33', '38', '43', '48', '53', '58') then '5' as ‘stemp’ If a in ('4', '9', '14', '19', '14', '29', '34', '39', '44', '49', '54', '59') then '7' as ‘stemp’ If a in ('5', '10', '15', '20', '25', '30', '35', '40', '45', '50', '55', '60') then '9' as ‘stemp’ I have this possibilities as a result.
View Replies !
Select Statement
I have a table containing names of people in the neighborhood. The table only contains first name and family names. How to get lists of first names grouped by the same family name ? This is similar to getting "sum" of salary grouped by a certain condition. Unfortunately, "sum" does not work for strings.
View Replies !
Need Help With SELECT Statement
I have 2 simple tables: emps and rprts. Table emps has: "ID", "name" (simple string), and some other irrelevant fields. Table rprts has: "ID", "emp_id" (indication of "ID" in emps table), "rprt" (10-digits time), and "type" (boolean). I want to select the fields "ID" and "name" from emps, and in addition - for every selected row I want to select rprts's "type", where "rprt" (or "id", doesn't matter) value in the highest. For example: emps: id name ------------------ 1 jeff 2 wanda 3 brad rprts: id emp_id rprt type ------------------------------------------- 1 3 df 1 2 1 ie 1 3 2 ei 1 4 3 fd 0 5 2 tg 0 6 3 gf 1 For this data, the following should be selected: id name type ---------------------------- 1 jeff 1 2 wanda 0 3 brad 1 I tried several things, but I want to keep it simple and readable, so I'll understand if I'll read it sometime in the future.
View Replies !
|
TRACKER
