UPDATE With Subselect?
I have two tables in my database
table1 holds a lot of info and table2 holds some metadata.
Especially I want to store the first and last (order by time) value from table1 in table2 pr. day and per key.
Say
Let's say table1 has the fields: id, datetime, key, value (AND MORE)
table2 has the fields: id, date, key, firstvalue, lastvalue (AND MORE)
I have a query that creates the rows in table2 already, but with firstvalue and lastvalue blank.
In PHP I could:
SELECT key, MIN(datetime) and MAX(datetime) GROUP BY LEFT(datetime,10), key
Iterate over my array, - find the value of each
UPDATE my table2
But that would be quite a few SELECT and UPDATE queries.
View 2 Replies (Posted: May 2, 2008, 10:38)
Sponsored Links:
Related Forum Messages for MySQL:
Update With Subselect
I must update some rows with a increasing value this way: This data: Id - Name - Location -------------------- 1 - ' ' - 1 2 - ' ' - 2 3 - ' ' - 1 4 - ' ' - 1 5 - ' ' - 2 The name value must be updatated as: Id - Name - Location -------------------- 1 - 'Machine n.1' - 1 2 - 'Machine n.1' - 2 3 - 'Machine n.2' - 1 4 - 'Machine n.3' - 1 5 - 'Machine n.2' - 2 I tried this query: UPDATE machine m1 SET m1.name = CONCAT("Machine n.",(SELECT COUNT(*)+1 FROM machine m2 WHERE m2.id<m1.id AND m2.location=m1.location)); But fails because the subselect is from the same table as the update... but how can i solve this problem? Can I or I need to do it programmatically? Thanks
Posted: September 30, 2009 04:29AM
View 1 Replies!
View Related
Update Using Subselect?
I have a table Users with cols: id, name, password, encrypted_pass I want to select the password and then encrypt it and update the same row setting encrypted_pass to that encrypted value. Trying: update users set encrypted_pass = select password from users; What am i doing wrong.
Posted: February 01, 2010 02:33PM
View 2 Replies!
View Related
Setting Up A Subselect In An Update Statement?
I WANT to do will be clear from my incorrect syntax below PHP Code: update games set `status`='offline'where (select COUNT() from characters where characters.gameID = games.id) = 0 I have a games table that lists my games, including a status text field that tells me if the game is still going on. I have a characters table that among other things has a gameID field that tells me what game they are currently in. This is going to be for a polling game, and I want a cron job (written in php) that will check for the last time the character has reported in and mark them offline and set their gameID to 0. This part is working well. I also want this cron job to set the status of all 'games' to 'offline' when there are no longer any players assigned to them. So I would want the 'games.id' part to reference the current row being processed by the update statement...
Posted: Jun 9, 2009, 22:11
View 5 Replies!
View Related
Subselect / AS
SELECT a.id (SELECT width, height, filename FROM photos WHERE user_id = a.id LIMIT 0,1) AS (width, height, filename) FROM users a ORDER BY a.datestamp DESC you can see my example, using with AS (example). How can i extract values from subselects?
Posted: May 14, 2007 10:06AM
View 3 Replies!
View Related
Getting Around Subselect
My knowledge of SQL is basic so I need some help developing a query. Suppose we have a table called BID where each row is a bid on an item up for auction. The relevant columns are BID_ID which is the primary key, ITEM_ID which identifies the item, and a BID_DATE which records the datetime of the bid. I would like to find the most recent bid for each distinct ITEM_ID in the table. I've worked out the query below which seems to do the job. However, I need to find a query that will work on a pre-4.1 server which does not support subqueries. Is there a way to re-state this query without using a subselect? Perhaps using some kind of join? SELECT * FROM BID, (SELECT ITEM_ID AS IID, max(BID_DATE) as MAXDATE FROM BID GROUP BY ITEM_ID) as MAXDATES WHERE (ITEM_ID=IID) and (BID_DATE=MAXDATE);
Posted: April 18, 2006 10:10AM
View 5 Replies!
View Related
Subselect
I had some SQL calls which worked fine on a v4.1 server and now I've moved to another one which is 4.0.24 and certain subselects no longer work. Is there any basic way to convert statements such as this: SELECT a.name, (SELECT COUNT(*) FROM table2 AS b WHERE b.id=a.id) as count FROM table1 AS a So that it conforms to the 4.0.x standard?
Posted: May 18, 2006 04:21PM
View 4 Replies!
View Related
Getting Rid Of Subselect
I have a table, which -- simplified -- looks like this: create table access_logs ( session_id varchar(32), request_uri varchar(32) ); Each pageview logs the users session-id + the request-uri. Now, to determine how many visitors followed a specific path, I need to select the number of sessions, which have a row including specific request_uri. This is my own feeble attempt, but I have a feeling that this could be rewritten to get rid of the subselects: select session_id from access_logs where session_id in (select session_id from access_logs where request_uri = "landing-page.php") and session_id in (select session_id from access_logs where request_uri = "exit-page.php") group by session_id;
Posted: May 14, 2007, 05:26
View 11 Replies!
View Related
Can Combine Subselect
I have to rewrite a query written for mysql5 to mysql4.0 which doesn't support sub select I have a query like this : select a, c, (select count(*) from A) as e from (select b, sum(e) as a from B where h = "foo" group by b) TABLEB, (select sum(d), count(*) as c from C where d = "bar") TABLEC I try to merge TABLEA and TABLE B in one request but sum() results are not correct ( sum(e) values become sum(e) multiplied by the number of rows of TABLEC) All the grouped values become a multiple of the real values (depending on rows number). Is is possible to transform this query into only one query for mysql 4.0 or will I have to split it into 3 query ?
Posted: Jul 15 10 at 16:37
View 1 Replies!
View Related
Return Row From Subselect
I have this MySQL DB scheme: users (id, login) coins (userid, value, curr) I need to write select which will return result: login and max coin he have and currency of this coin. I've tryed something like that: SELECT login, ( SELECT value, curr FROM coins WHERE coins.userid = users.id ORDER BY value DESC LIMIT 1 ) AS ROW(value, curr) FROM users Its not working... I'll recieve error, that "Operand should contain 1 column(s)". I expected it, but I dont know any way, how to make it. So i guess: Is there any way to return multiple-column-single-line (row) from subquery to parent query? (Yes, I can use two subqueries, but its not effective.)
Posted: Feb 18 at 1:24
View 1 Replies!
View Related
Subselect In A Join?
I've a little Problem with a statement: SELECT p1.Modell_nr, 1.ProductID, p2.count_modlieffarbe_vl, concat(p1.Modell_nr,'_',p1.LiefFarbe) as modfarb_id1............ ... it works without error-message. ... but get only Modell_nr, ProductID and modfarb_id1 as cols in my results. Why I dont see count_modlieffarbe_vl in my results?
Posted: Feb 28 at 12:26
View 1 Replies!
View Related
Subselect/left Join
I have a table like this | ID | THING | NUMBER | --------------------------------------------------------------- | 1 | white | 1 | | 2 | white | 2 | | 3 | green | 1 | | 4 | green | 3 | | 5 | brown | 1 | | 6 | brown | 4 | and I want to get just white back if I know two numbers are 1 and 2 or green back if I know the nubmers are 1 and 3. Its mysql 4.1 so I am allowed subselects or left joins. I am drawing a blank!?
Posted: January 11th, 2006, 05:46 PM
View 5 Replies!
View Related
Joining A Subselect To A Table
Is it possible to join the results of a SELECT with another table. Like this: SELECT * FROM table1 LEFT OUTER JOIN (SELECT * FROM table 2) I know I need to link the column but am not sure how. Is this possible?
Posted: Oct 29 10 at 14:51
View 2 Replies!
View Related
Subselect Column Invalid?
I have a sql issue with column names in a subselect im guessing its because it has yet to be assigned that name but I cannot work out how to rearange it. select Distinct Captains.Name, Captains.Team, (select count(Winners.Name) from (select HomeTeamCaptain As Name from fixture where fixture.HomeTeamCaptain = Captains.Name And fixture.matchResult = fixture.HomeTeam UNION ALL select AwayTeamCaptain As Name from fixture where fixture.AwayTeamCaptain = Captains.Name And fixture.matchResult = fixture.AwayTeam) As Winners) As Winners From (select fixture.HomeTeamCaptain As Name, HomeTeam As Team From fixture UNION ALL select fixture.AwayTeamCaptain As Name, AwayTeam As Team From fixture) As Captains order by Name; The "Captains.Name" is the issue I need it to run the Count - Subselect but cannot get hold of its value!
Posted: Feb 22 10 at 12:49
View 1 Replies!
View Related
Multiple Column Subselect In 5 (5.1.42)
This one seems to be a simple problem, but I can't make it work in a single select or nested select. Retrieve the authors and (if any) advisers of a paper (article) into one row. I order to explain the problem, here are the two data tables (pseudo) papers (id, title, c_year) persons (id, firstname, lastname) plus a link table w/one extra attribute (pseudo): paper_person_roles( paper_id person_id act_role ENUM ('AUTHOR', 'ADVISER') ) This is basically a list of written papers (table: papers) and a list of staff and/or students (table: persons) An article my have (1,N) authors. An article may have (0,N) advisers. A person can be in 'AUTHOR' or 'ADVISER' role (but not at the same time).....................
Posted: Mar 15 10 at 20:42
View 2 Replies!
View Related
Subselect As Condition In WHERE Clause?
How would I use a subselect in a WHERE clause? e.g. WHERE pos.jobRef = invoices_out.jobRef AND enquiries.id = pos.enqRef AND DATE(invoices_out.invoiceDate) = CURDATE() AND (SELECT MAX(dateCreated) FROM pos) = invoices_out.jobRef Because I just want it to base its selection on the latest date result.
Posted: June 09, 2011 03:01AM
View 2 Replies!
View Related
Select Columns Based On Subselect
Is it possible to do the following: select (select from factors where type='factor_1') from survey_results where last_name='some_name'; I get the error: "#1242 - Subquery returns more than 1 row" Can I convert the rows to a list? More info if needed: I have two tables, 'factors' and 'survey_results'. A survey_result row contains a list of factors (> 30). Factors can have different types. For a survey result I'd like to select only the columns related to a certain type of factor and analyze just those factors (e.g., find the min and max scores). I don't want to hard code lists of factors of certain types to use in my outside select statement. I'd like to do it dynamically based on the factors table.
Posted: January 29, 2009 10:40AM
View 3 Replies!
View Related
Complex Select (Possible Subselect Needed?)
I have a table, b5_assignment_lookup, that is used elsewhere as a lookup but I'm trying to use the data contained by itself here. The table: CREATE TABLE b5_assignment_lookup ( as_id int(11) NOT NULL auto_increment, as_blog int(11) NOT NULL default Ɔ', as_blogger int(11) NOT NULL default Ɔ', as_milestone enum('start','finish') NOT NULL default 'start', as_timestamp timestamp NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY (`as_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=222 ; By running this query, I get the following resultset: mysql> SELECT * FROM b5_assignment_lookup WHERE as_blog = ྕ' AND as_timestamp <= ��-09-30' +--------+----------+-------------+---------------+---------------------+ | as_id | as_blog | as_blogger | as_milestone | as_timestamp | +--------+----------+-------------+---------------+---------------------+ | 87 | 89 | 41 | start | 2006-05-01 00:00:00 | |208 | 89 | 41 | finish| 2006-09-02 11:55:27 | |209 | 89 | 103 | start | 2006-09-02 11:55:27 | +--------+----------+-------------+---------------+---------------------+ 3 rows in set (0.00 sec) What we have here is that Blogger 41 began writing on Blog 89 on May 1, and on Sep 2 Blogger 103 took over for Blogger 41. What I really need to grok is all bloggers who blogged all or a part of a range of dates. For example, I really want to find out which bloggers blogged on blog 89 for all or part of 2006-09-01 to 2006-09-02. I use this dataset as an example, but we might have completely different circumstances such as Blogger 20 being replaced on the third day of the date range, being replaced by blogger 29 for 10 days and then quitting due to lack of time and the blog going unmanned for 5 days before Blogger 20 decides to step back in on day 25. The flags are the start/finish, obviously.
Posted: Sep 18, 2006, 06:53
View 2 Replies!
View Related
Multiple Subselect Vs Additional Query
I have an existing single row LIMIT 1 query. I also have another table that has 3 multi row columns that I need to count that has a relation to one column in the original table. I can accomplish this with 3 subselects in the original query or I can add another query. I guess my question would be are the 3 subselects going to add enough overhead that an additional query would be warranted? Or is it still more efficient to use a subselect than add a query? I can't change the original query and I am accessing it through hooks so my options are limited there.
Posted: May 16, 2008, 22:46
View 5 Replies!
View Related
Fetch Subselect Result And Use It In WHERE Clause?
I want to both fetch and use in a where clause the value returned from a sub-select in MySQL. Is this possible? It seems unecessary to write the sub-query out twice - however if I need to, will MySQL be clever enough to only run it one? I have tried the following which does not work: SELECT (SELECT 1 FROM table WHERE somereallycomplicatedclause = 'something') AS subselectresult FROM content WHERE subselectresult = 1 This generates this error: #1054 - Unknown column 'subselectresult' in 'where clause'
Posted: Jun 4 09 at 10:27
View 2 Replies!
View Related
Subselect Query Doesn't Work On Db 4?
The following sql query works fine on my development server running mysql 5, but when I try it on my live server running mysql 4 it throws an error,how to adapt my query to run on mysql 4? select * FROM Properties WHERE propertyid IN (select id from todelete)
Posted: Mar 4 at 13:02
View 2 Replies!
View Related
Query - Db Subselect Performance Question?
I need advice regarding subselect performance in MySQL. For a reason that I can't change, I am not able to use JOIN to create quesry filter, I can only add another AND clause in WHERE. What is the peformance of: select tasks.* from tasks where some criteria.............
Posted: Dec 4 08 at 15:54
View 4 Replies!
View Related
|
TRACKER
