Insert With Nested Select Problem
I want to implement a query similar to this in mysql:
insert into table1 (field1)
(select t2.field2
from table2 t2
where t2.field3="string" and t2.field4=table1.field4)
the reference to table1 from the inner query (table1.field4) does not work.
Is there a way to achieve this?
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Insert Multiple Rows With One Insert Stmt And Nested Select
I'm trying to insert several rows into a table using only one insert statement: insert into component_feature values (select 3,1,sf.software_feature_id,1 from software_feature sf where sf.software_id = 1) When I run the select statement alone, I get the result I want: +---+---+---------------------+---+ | 3 | 1 | software_feature_id | 1 | +---+---+---------------------+---+ | 3 | 1 | 0 | 1 | | 3 | 1 | 1 | 1 | | 3 | 1 | 2 | 1 | +---+---+---------------------+---+ But when I run the complete statement I get: ERROR 1064 (42000): 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 'select 3,1,sf.software_feature_id,1 from software_feature sf where sf.software_i' at line 1 This query is part of a PHP application I'm building. I'd rather not have to write a PHP loop to do multiple inserts.
View Replies !
View Related
Nested Select Using Like
I am trying to use the results of a nested select statement in the 'parent' statement using 'LIKE' as per: SELECT * FROM t1 WHERE c1 LIKE (SELECT c7 FROM t2 WHERE ID = 987); Does anyone know if this is possible? I am getting empty sets when I experiment with the placement of the % signs. For example: SELECT * FROM t1 WHERE c1 LIKE "%(SELECT c7 FROM t2 WHERE ID = 987)%"; Empty set (0.01 sec) Also I have tried to assign the result of the nested select to a variable like: SELECT @v := c7 FROM t2 WHERE ID = 987; But again then have no idea how to put this variable "@v" into a statement using LIKE. Have tried: SELECT * FROM t1 WHERE c1 LIKE '%@v%'; Empty set (0.00 sec)
View Replies !
View Related
Nested Select OR What To Use?
My table: CatID, CatTitle, ParentID Some example data: 1, Movies,0 2, Mymovies,1 3, Jaws, 2 4, Alien, 2 5, Predator,2 6, Final fantasy, 1 7, X-files,1 What i want in my output: Movies (1), Mymovies (2), Jaws (3) Movies (1), Mymovies (2), Alien (4) Movies (1), Mymovies (2), Predator (5) I want to use the output data to make a click-able tree level. example: Movies Mymovies Jaws link?catid=1 link?catid=2 link?catid=3
View Replies !
View Related
My Nested SELECT
SELECT COUNT(1) as numapp FROM post_jobs (SELECT COUNT(pj.1) as numnot FROM post_jobs as pj WHERE member_id='m2e4m387' AND pj.approval = 0) WHERE member_id = 'm2e4m387' AND approval != 0 I've looked stuff up online and have re-arranged this in several different ways. Can count not be used within a nested SELECT?
View Replies !
View Related
Nested SELECT :: Select All Years Except Last
I'm new to MySQL and want to do a nested SELECT statement. i want to get all years stored in the database except the last year: (content is the table and null values exists) SELECT DISTINCT YEAR(content.date) FROM content WHERE(((YEAR(content.date)) IS NOT null)) AND YEAR(content.date) < (SELECT YEAR(MAX (content.date)) FROM content)) ORDER BY YEAR(content.date) DESC What is wrong
View Replies !
View Related
Nested Select Statement
I am working on a feature for my website (MyRoladex.com) where i have a script run through the database once per hour, look to see who has events scheduled that are within the next hour, then look to see what their email/sms/text message preferences are, then email/text them a notification that it is close to time for their scheduled event. Under the Accounts table, i have 5 notable fields: uid, email, mobile, timezone, messagetype Under the DatebookEvents table, the important field is, eventdate (besides 'uid', obviously), which is is YYYY-MM-DD HH-mm-SS format. I did not want to calculate the time zone difference for each user, and then compare it to each of the event dates, and then to the system time, just to see if a notification should be sent, as this would be an immense waste of cpu and bandwidth. The next best thing that i though of was to STORE the event date PLUS or MINUS the time zone difference, whenever the user creates a new event, and then, whenever the user displays their events, it would ADD or SUBTRACT the time zone to the event date, so that it will display correctly for them, AND when the cron job runs once every hour, it will ONLY have to compare the eventdate field with the system time. This is better because the cron job will run once per hour, and have to work with, perhaps, thousands of records, whereas a user may only display his records once or twice per day, thus saving cpu and bandwidth overhead When i was writing the query for the displaying of event details, where i take the event date for the entry they are viewing and ADD or SUBTRACT their time zone to it so that it displays properly according to their time, i came across this problem: Select eid,(eventdate + hour(select Accounts.timezone from Accounts where Accounts.uid='666')),left(note,40) from DatebookScheduler where uid='666' Returns: #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 'select Accounts.timezone from Accounts where Accounts.uid='666')),left(note,40) ' at line 1
View Replies !
View Related
Nested SELECT Statements
I am trying to form a query from a search form where users can search by various catergories. The version of MySQL I am using is 4.0.18 and doesn't seem to support nested select statements since it returns a syntax error at the point I try to use one. I can't use temporary tables or views as there are no permissions for create at the php page level. I really don't know what else to try as my SQL knowledge is not that vast. The query I have built so far is: SELECT DISTINCT clientid, name FROM (SELECT tbl_clientdetails.* , id, date_made from tbl_clientdetails, contact_made WHERE tbl_clientdetails.clientid = contact_made.clientid) AS t1 WHERE id <> any (SELECT id from contact_made where date_sub(curdate(), interval 90 day)<= date_made) AND name like '%boat%' OR industryType like '%boat%' OR comments like '%boat%' OR productBrand like '%boat%'
View Replies !
View Related
How To Improve On A Nested Select
I'm working on a simple data import tool, and I need to insert email addresses from table two into table one, if they don't already exist in table one. I figured this was a pretty easy nested select statement, but what I'm doing is getting my site taken off line for exceeding the CPU limit. Here's the SQL I'm using to get the new email addresses: SELECT distinct value, id FROM table_two WHERE name = 'email_address' AND value NOT IN (SELECT DISTINCT subscriber_email FROM table_one) There are about 4600 rows in table one, and 145,000 rows in table two. Does this seem like it would be a burdensome query? I'm not a SQL expert my any means, so is there a better way to go about this? It seemed like a simple one to me. Maybe my web host is just stingy with the CPU time.
View Replies !
View Related
Using Update With Nested Select
I've got two tables. wp_gameUsers & wp_achievements. Each have a field called points, and I would like to be able to pull a selected record of points from wp_achievements and add it to a selected tally in wp_gameUsers. Like so; UPDATE wp_gameUsers SET points = points + (select points from wp_achievements where achieve_ID = 8 ) where id = 1 Except whenever i do this it updates the number twice, Eg; 250 points from wp_achievements = 500 points.. The ID is unique I just can't figure out what is going on.
View Replies !
View Related
Nested Select On Same Table
I'm stuck with an SQL query. i want to fetch 16 username and avatar from my table where 8 entries should follows one condition and remaining 8 follow another one. more descriptively, 8 avatar- selected images by user. 8 avatar- default one.
View Replies !
View Related
Figure Out Nested SELECT Statements
I try to figure out how to use a nested "SELECT" statement after the "IN" predicate. For example, when I try this code, it doesn't return anything although it should: SELECT book.IDLivre, aut.Prenom, aut.Nom FROM livre book, livreEcritPar ecr, auteur aut WHERE ecr.IDLivre = book.IDLivre AND aut.IDAuteur = ecr.IDAuteur AND book.IDLivre IN ("SELECT book.IDLivre FROM livre book, livreEcritPar ecr, auteur aut WHERE aut.Prenom like '%$firstName%' AND aut.Nom like '%$name%' AND ecr.IDAuteur = aut.IDAuteur AND book.IDLivre = ecr.IDLivre"); So, my question is the following: How should I change syntax in order to make this bunch of code work? I mean, under Oracle SQL, this syntax would be legal and work perfectly, so I'm confused how to solve my problem.
View Replies !
View Related
Syntax Error With Nested SELECT
Working with MySQL 4.0.27-standard via phpMyAdmin (and Perl) I've been getting error #1064 for certain SQL statments, and have traced the problem down to nested SELECTs. This works fine: SELECT `CITY` FROM `pa_geotags` WHERE `PLACE_ID` = 1303 ; Whereas this (just a trivial example to show the point): SELECT `CITY` FROM `pa_geotags` WHERE `PLACE_ID` = (SELECT 1303) ; gives rise to: #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 'SELECT 1303 ) LIMIT 0, 30' at line 1 I've tried several other (more complex) nested SELECTs, all give exactly the same type of error. phpMyAdmin seems to be non-optionally adding 'LIMIT 0, 30' to the end of my SQL before submitting it, but I don't think that's the problem; I sent the same SQL to the same database via a PERL script and also got a syntax error reported.
View Replies !
View Related
Nested Select Statement Error
the mysql that is loaded on the host i am on is mySQL 4.0.24 whereas the one i am using is mySQL 5.0.27 on my computer. i am having some weird problems where nested statements like this are not working on the server i am on... select * from table1 where id = (select id from table2 where user = 1); this sql statement works fine on my computer but not on the server. it says there is a syntax error when it is run on the server. but this sql doesn't look wrong to me, or it does? i have a feeling that sub queries is not implemented in mysql 4.0 and earlier....but i really don't wish to change all the sql in my scripts to the old method where i have to select * from table1, table2 where table1.id = table2.user; because it will be quite troublesome...
View Replies !
View Related
SHOW TABLE STATUS & The Nested SELECT Statement
The "SHOW TABLE STATUS" command seems to return a result set, just like "SELECT * FROM SomeTable". Therefore, since the following is valid: SELECT * FROM (SELECT * FROM SomeTable) I inferred that this functionality would extend to the SHOW statement: SELECT * FROM (SHOW TABLE STATUS FROM SomeDataBase) But such a command gives an invalid syntax error. I want to query the data_length from a few tables, place those values into temp variables and then run a second SQL command using those values. Is what I'm trying to do possible?
View Replies !
View Related
Nested Select Statement Taking Hours To Execute
I am running a sub select statment so I can compare a couple of tables. If I run the statement from two test tables of 1000 records in each, it will take nine seconds to get the results that I need. However, if I take the same statement and point it to the two tables which contain all the data I need to run against (50560 and 161680 records respectively), it takes hours to run...how long I'm not sure. I fired the statement off yesterday afternoon @ 3pm and it is currently 11:30am the following day...the select statement is still executing. Does anyone have performance on something like this?
View Replies !
View Related
Testing For An Empty Set Returned By A Nested Select Statement
For those of you following the "While Loop Within Select" thread... I've been restructuring my database to incorporate the changes. Now I've got a table of skipped stops rather than a table of stops (I need it that way for various reasons) and I need to return the schedule to the user as long as the user's stop isn't in the list of skipped stops. SO say something like: SELECT yada yada yada FROM schedule AS 's1' WHERE ( SELECT skippedstop_id FROM skippedstops WHERE schedule_id=s1.schedule_id AND location_id=$origin ) == "EMPTY SET" Only I have no idea how to actually test for the empty set from the nested select...
View Replies !
View Related
Nested Sets And Filtering Conversion To Nested Arrays, How?
I am currently working on a project where we want to reuse a tree structure stored in the database. The tree are basically groups/categories of all the content available within the project. Now we want to reuse this data in forum and only show the featured items/nodes. An idea of the tree structure is: + root + Music (f) + Genre - Electronic (f) - Classical (f) Now the problem is that we don't wan to show the whole tree structure within this new forum. But only the ones for example, which are flagged to be featured. For this, we have a field in the groups table. Only I happen to have some problems with reusing this tree hierarchy: The main problem I have is that when I get the whole tree structure using the query SELECT * groups ORDER BY left_id then I have the whole tree. Only when I add "WHERE featured=1" then the left and right node values are becoming invalid because I filtered out possible child or parent nodes. I am curious if anyone could help me how to get the structure in such way I can easily reuse it. I would love to have the featured nodes in a nested array. Something like: root children: music children: electronic, classical etc.
View Replies !
View Related
INSERT INTO SELECT Taking Forever But SELECT Part Is Fast?
I have the following mysql code to insert about a million rows. This takes quite a long time (over 50 min). However, when I just run the query alone without the INSERT command, the query itself only takes under 5 minutes. Is there a way I could speed up the inserting?? insert into tblc select tbla.* from tblb inner join tbla on tbla.issuer_id = tblb.issuer_id;
View Replies !
View Related
Nested Select :: Checkboxes Checked / Not Checked
I'm trying to display a bunch of checkboxes in php and have them checked/not checked based on the results of the following query but I'm getting syntax errors. This would work in mssql how would I accomplish the same thing in mysql? SELECT tblOrg_Type.ID, tblOrg_Type.Org_Type, (Select 'Checked' from tblContactOrgType where OrgID = 8 AND tblOrg_Type.ID = tblContactOrgType.OrgTypeID ) AS Checked FROM tblOrg_Type; Table Structures: tblOrg_Type ID Org_Type tblContactOrgType ID OrgID OrgTypeID
View Replies !
View Related
INSERT SELECT
I need to insert a way that mysql doese'nt complain when i copy some records that have the same id (or that it just gives it an id according to the AUTO_INCREMENT) Thanks again //Lars Rasmussen -----Oprindelig meddelelse----- Fra: Jay Blanchard [mailto:jay.blanchard@niicommunications.com] Sendt: 13. august 2003 19:59 Til: Lars Rasmussen; mysql@lists.mysql.com Emne: RE: INSERT .... SELECT [snip] I used this command: INSERT INTO nye_opskrifter SELECT * FROM opskrifter where id in($numbers) But now it gives this error: Column count doesn't match value count at row 1 [/snip] * does not return a specific number of columns, the work around is to specify the columns explicitly INSERT INTO nye_opskrifter SELECT foo, bar FROM opskrifter where id in($numbers).
View Replies !
View Related
INSERT... SELECT
Im trying to insert a bunch of rows into a table. If the row already exists id like to update the row 'counter'. For example... INSERT INTO table1 SELECT field1, field2 FROM table2 ON DUPLICATE KEY UPDATE field2 = 1 Is it possible to use both INSERT... SELECT... with ON DUPLICATE KEY? I cant get it to work.
View Replies !
View Related
Insert Into Xyz Select * From Abc
Have two tables "abc" and "xyz", where "xyz" is a superset, column-wise, of "abc". Is there any simple way to inject all the rows of "abc" into "xyz"? Tried "insert into xyz select * from abc" but got a complaint of "column count doesn't match value count at row 1", which of course is true. Any way to tell SQL to simply set defaults for the missing columns?
View Replies !
View Related
INSERT And SELECT
I'm wondering if it is possible to run an INSERT...SELECT query where I am grabbing data from Database1 with the SELECT query and inserting this into tables in Database2 with the INSERT.I saw one of the comments over at: http://dev.mysql.com/doc/refman/5.0...ert-select.html (third or fourth comment down) suggested that this was possible, al-be-it this is in the manual for version 5 of MySQL and I am using 4.1.11
View Replies !
View Related
INSERT/SELECT Row
I'm trying to duplicate multiple rows from table_parent into table_child. However, I would like to set the value of column1 normaly. (i.e. insert a static value not retrieved from table_parent) Both column1 and column2 are primary keys in both tables. Column1 needs a different value in table_child from what it has in table_parent. The SQL for a direct duplication would be: PHP Code: mysql_query("INSERT INTO table_child(column1, column2) SELECT column1, column2 FROM table_parent WHERE column1 = $var_column1", $connection); How do I change the values being inputted from table_parent.column1, table_parent.column2 to $var_phpVariable, table_parent.column2? I can do this simply enough with a loop but would rather not spend up to 30+ queries when it could be done in one query.
View Replies !
View Related
Insert Using Select
Can anyone let me know if it possible to formulate an insert query that that can use both select and values, so that I can get certain data from the another table and have fixed values for some of the fields in the insert query? I guess one way would be to do an insert for select statement, then an update for fixed values.
View Replies !
View Related
INSERT With SELECT
I've been working on a stored procedure which takes rows from one table and inserts them into a temporary created memory table, but I've stumbled accross a small roadblock (I hope at least)... Here's the statement I'm trying to exectute INSERT INTO temptable SELECT * FROM template WHERE type_name like '%jack%' ORDER BY RAND() LIMIT 1; if I take out the ORDER BY RAND() it works, but natrually it always selects the first record, is it possible to have the ORDER BY RAND() in that select, or would I need to do it another way...
View Replies !
View Related
Insert Using A Select
Can seomeone tell me what I'm doing wrong? Here's a simplified version of what I'm doing: CREATE TABLE `test1` ( `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `val1` VARCHAR( 5 ) NOT NULL , `val2` VARCHAR( 5 ) NOT NULL ) ENGINE = MYISAM ; CREATE TABLE `test2` ( `val1` VARCHAR( 5 ) NOT NULL , `val2` VARCHAR( 5 ) NOT NULL, `val3` VARCHAR( 5 ) NOT NULL ) ENGINE = MYISAM ; insert into test2 (val1, val2, val3) values ("some", "test", "values"); ******** Then I try to do this, am I doing something wrong or is this not possible? insert into test1 (test1.val1, test1.val2) values (select test2.val1, test2.val2 from test2 where 1);
View Replies !
View Related
Insert Into Select
I've search and search and I haven't found the answer. I am new to MySQL but not new to SQL. I teach an SQL class. This term, we choose to use MySQL. We are trying to execute this: Insert into temp_table select zip from customer temp_table is being created on the fly. We get the following error: Error 1146 (42S02) ...table 'temp_table' doesn't exist.
View Replies !
View Related
Insert And Immediate Select
I add a bunch of records using ExecuteNonQuery() in C# (OdbcCommand). Immediately after the INSERT, I do a SELECT (with a different connection) on the same data. And once in a while (not often, but still...), the records just inserted is not included. If I try again, it works. Each time, "rows affected" return a value greater than 0, so the data should be there somewhere... Is there a way to make sure that records are "firmly" added to the database before I go ahead with the subsequent SELECT (preferably without using transactions)?
View Replies !
View Related
INSERT ... SELECT ... AND
i try to do a subquery on INSERT ... SELECT i need the id of two entrys from one table and want to insert them into a nother table. also i want to insert a value to the same table. this syntax doesnt work... INSERT INTO a (ID1, ID2, C) SELECT lemma.id FROM lemma WHERE lemma.lemma="my" AND SELECT lemma.id FROM lemma WHERE lemma.lemma="question" AND VALUE (4); how can i do this?
View Replies !
View Related
INSERT INTO SELECT
I am new to mysql and would appreciate someone helping me out with this converting this statement from postgresql to mysql. INSERT INTO ossurjoinquestions SELECT *, RANDOM() as random_sort, trunc(1144355460, 0) as instanceid FROM ossurcommonquestions WHERE tid = '3'; So far I have INSERT INTO ossurjoinquestions( tid, question, ans1, ans2, ans3, ans4, ans5, corans, image_path, random_sort, instanceid ) SELECT tid, question, ans1, ans2, ans3, ans4, ans5, corans, image_path FROM ossurcommonquestions WHERE tid = '3' What I am not sure how to do is add RAND() as random_sort or trunc($var,0) as instanceid.
View Replies !
View Related
Insert Then Select
if I have table with a field named myCounter and i auto increments when I do an insert, what is the best way to get that value. This is what I am doing now. myTime = Now Insert into myTable (Value1, Value2) values (myTime, "myValue") 'then I do this right after to get myCounter Select myCounter from myTable where Value1 = myTime and Value2 = myValue
View Replies !
View Related
Insert Into ... Select
I am looking at creating a simple versioing system for a small CMS I have written. An obvious way to do this would seem to me to do a INSERT INTO .. SELECT whenever the page is amended, but before the amendments are stored. ie copy the data first. But although the structure of the table is virtually the same, I need to add columns to it for the actual versioin number eg. Table A has 'id, title, content, editdate' Table B has 'uniqueid, version,id, title, content, editdate' Though the position of 'version' can actually be anywhere.(uniqueid may, or may not, be required) Can I achieve this in one? and if so how?
View Replies !
View Related
Insert From Select
INSERT INTO thistable (SELECT * FROM thistable WHERE id=Ƈ') The problem is, there is a unique key ID, thus that statement generates an error duplicate key. I know that I can change the sql by listing all the fields, but is there any other way? because if I list all the fields, firstly there are many fields to be type in and secondly if I happened to add/edit some fields later and I forget to change this sql, then it wil produce wrong result.
View Replies !
View Related
INSERT SELECT Not Working
I have some trouble since i've upgraded from 3.23.49 to 4.0.13. I used this command: INSERT INTO nye_opskrifter SELECT * FROM opskrifter where id in($numbers) But now it gives this error: Column count doesn't match value count at row 1 I tried IGNORE, but it's just not working. I hope anyone of you got a workaround for this problem, i did'nt find any in the manual.
View Replies !
View Related
INSERT SELECT In One Query
I keep getting an error with this query: Insert into new_voter (salutation, firstname, lastname, electoral_district, poll_number, birthdate, gender, occupation, party_affiliation, ph_home, email) values select (txtSalutation, firstname, lastname, txtElectDistrict, txtVotingArea, dtBirth, txtGender, txtOccupation, nPartyId, txtPhoneDay, email) from person Can someone spot the error (presumably with the syntax).
View Replies !
View Related
How To Insert If A Value Does Not Exist And Select If It Does?
Basically, I am doing a users table with an ID and a name for now. I want to create a stored function that returns the ID of a user and creates the user if it does not already exist. The way I do it for now is: Select the user record by name. If we had one, return its ID. If not, insert a new user record and return its ID. But I am worried about concurrency. What if another thread runs the same function while this one is between steps 2 and 3? TWO user records would be created for the same user. That would be bad. I added a UNIQUE index to the username, but this means that the stored function will just error in a case like this, instead of returning the already existing user. This is also undesirable.
View Replies !
View Related
INSERT Values From SELECT
This works: INSERT INTO content (group_id) SELECT max(id)+1 FROM content; But how do I enter into more than one field? This doesn't work. INSERT INTO content (group_id, content_id) (SELECT max(id)+1 FROM content), 55; OR INSERT INTO content (group_id, content_id) VALUES ((SELECT max(id)+1 FROM content), 11); You can't specify target table 'content' for update in FROM clause
View Replies !
View Related
Insert...select...error
I am getting this error msg: Subquery returns more than 1 row ("INSERT INTO customerbook (booking_ID,room_Type) values((select booking_ID from bookings where booking_ID = bookings.booking_ID),'$a')") ID is reference from table1... I m tryin to get this output: ID type 1 single 1 ensuite any idea?
View Replies !
View Related
Select From Table Before Insert
I am trying to create a trigger that will select the value for an integer from the table on which the insert is occuring, increment it by one, and insert the value into the new row on the same table. Basically, we have a user referral id. I want to grab the rank of the referral user, increment it, and insert it into the rank of the new user. i cannot figure out how to do this for the life of me. Code:
View Replies !
View Related
Insert With Both Values And Select
I need to insert multiple values in a table I use an insertstatement like this one insert into table(name, jobID) select 'Jon', id from jobs where name = 'programmer'; However, if I have a value before the condition like insert into table (jobID, name) select id from jobs where name='programmer', 'jon2'; it does not work and I need such a statement to work cuz I get different data with a lot of fields and I do not know in advance which will be fields with have direct values and hich will ghave to get data from other tables
View Replies !
View Related
Concurrent Select And Insert
we have a system that encompasses hundreds of nodes. We use a MySQL db to archive messages prouduced by different applications sent via an in-house transport mechanism that follows the producer/server/consumer scheme. As you can imagine the message transmission rate can be fairly large. A PHP based tool can be used to browse the messages stored on the database. An application runs in the MySQL server to get the messages and put them on the db. The problem is that when the messages are browsed with th PHP-based tool, the mysqld SELECT thread blocks the mysqld INSERT threads created by the 'Receiver' application. This, cannot send an acknowledge to the transport mechanism and the conection is broke after a 1 sec. timeout, in other words it does no longer receive messages. My question is then if there is a way of forcing the INSERT opeartion even when a lenghty SELECT is taking place. I'm using MySQL 3.24 and have tried INSERT HIGH_PRIORITY and INSERT DELAYED with no success.
View Replies !
View Related
|
TRACKER
