473,324 Members | 2,313 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

insert with nested select problem

7
I want to implement a query similar to this in mysql:


Expand|Select|Wrap|Line Numbers
  1. insert into table1 (field1)
  2.   (select t2.field2
  3.   from table2 t2
  4.   where t2.field3="string" and t2.field4=table1.field4)
  5.  
the reference to table1 from the inner query (table1.field4) does not work.

Is there a way to achieve this?

Thank you.
Sep 20 '07 #1
6 6230
pradeep kaltari
102 Expert 100+
I want to implement a query similar to this in mysql:


Expand|Select|Wrap|Line Numbers
  1. insert into table1 (field1)
  2.   (select t2.field2
  3.   from table2 t2
  4.   where t2.field3="string" and t2.field4=table1.field4)
  5.  
the reference to table1 from the inner query (table1.field4) does not work.

Is there a way to achieve this?

Thank you.
Hi,
In the above query first the SELECT statement is executed and then the result set is inserted in the TABLE1.

So, it is clear that the "table1" should also be included in the Select statement. The query would look something like:
Expand|Select|Wrap|Line Numbers
  1. insert into table1 (field1)
  2.   (select t2.field2
  3.   from table2 t2, table1
  4.   where t2.field3="string" and t2.field4=table1.field4)
  5.  
I hope this solves your problem.

- Pradeep
Sep 20 '07 #2
mwasif
802 Expert 512MB
Hi faif and pradeep kaltari,
Kindly use appropriate CODE tags when posting source code.
Sep 20 '07 #3
faif
7
Hi,
In the above query first the SELECT statement is executed and then the result set is inserted in the TABLE1.

So, it is clear that the "table1" should also be included in the Select statement. The query would look something like:
Expand|Select|Wrap|Line Numbers
  1. insert into table1 (field1)
  2.   (select t2.field2
  3.   from table2 t2, table1
  4.   where t2.field3="string" and t2.field4=table1.field4)
  5.  
I hope this solves your problem.

- Pradeep
Thank you for the answer, but unfortunately I've already tried this and it is not what I'm looking for.

I want to copy the values of the fields of table2 to the fields
of table1. I use the t2.field4=table1.field4 because I want to copy the contents in the appropriate records, and don't create new. When I'm using a copy of table1
in the inner query, new records are created in table1, and this doesn't solve my problem.
Sep 21 '07 #4
amitpatel66
2,367 Expert 2GB
Thank you for the answer, but unfortunately I've already tried this and it is not what I'm looking for.

I want to copy the values of the fields of table2 to the fields
of table1. I use the t2.field4=table1.field4 because I want to copy the contents in the appropriate records, and don't create new. When I'm using a copy of table1
in the inner query, new records are created in table1, and this doesn't solve my problem.
Then you need to use UPDATE insted of INSERT.
Try below code:
Expand|Select|Wrap|Line Numbers
  1. UPDATE table1 SET field1 = (SELECT t2.field2 from table2 t2 WHERE t2.field3="string" AND t2.field4=table1.field4)
  2.  
Sep 21 '07 #5
faif
7
Then you need to use UPDATE insted of INSERT.
Try below code:
Expand|Select|Wrap|Line Numbers
  1. UPDATE table1 SET field1 = (SELECT t2.field2 from table2 t2 WHERE t2.field3="string" AND t2.field4=table1.field4)
  2.  
Thank you. I tried but mysql doesn't like it... :(
The error message is:

#1064 - You have an error in your SQL syntax near 'SELECT t2.field3
FROM table2 t2
WHERE t2.field' on line 2

Info: The inner query returns multiple rows.
Sep 27 '07 #6
faif
7
Finally, a two-step solution has been found. The solution is:

Expand|Select|Wrap|Line Numbers
  1. CREATE TEMPORARY TABLE temp
  2.   (select t2.field2, t2.field3
  3.    from table2 t2
  4.    where t2.field3="string");
  5.  
  6. update table1 t1, temp t
  7. set t1.field1=t.field3
  8. where t1.field2=t.field2;
  9.  
Thank you all for the help.
Sep 27 '07 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Mark | last post by:
hey there, i'm trying to move one record from one table to the next, so i'm doing this by doing an insert into table, then delete from the previous table. Only thin g is on the insert i want to...
3
by: Almund Sebi | last post by:
Hello I use DB2 UDB V8.1.5. Using embedded SQL (SQLJava), I insert a row into a table which has some foreign keys and check constraints. The insert works fine, but if I encapsulate the insert...
4
by: jjack100 | last post by:
I have a DropDownList that is nested inside a Repeater. The datasource of the DropDownList is declared in the aspx, not the codebehind. So we have this: <asp:Repeater ID="rptOptions"...
2
by: Scarab | last post by:
Hi,all, When I use following sql, an error occurs: insert into #tmprep EXECUTE proc_stat @start,@end There is a "select * from #tmp " in stored procedure proc_stat, and the error message is :...
0
by: Scarab | last post by:
Hi all, When I use following sql to get data in stored procedure, error occurs: insert into #tmp EXECUTE dbo.prc_1 @date1,@date1 Here is the source code, Thanks CREATE TABLE ( NULL , ...
29
by: pb648174 | last post by:
I have the following basic statements being executed: Create a temp table, #TempPaging Insert Into #TempPaging (Col1, Col2) Select Col1, Col2 From SomeOtherTable Order By Col2, Col1 Select...
1
by: masri999 | last post by:
Hello, I want to share my experiences about using insert into exec which may help others . Using SQL Server 2000, SP3 . Two Proceduers - Parent SP caliing a Child SP (nested ) . No...
8
by: nano2k | last post by:
Hi Shortly, I keep invoices in a table. Occasionally, someone will fire the execution of a stored procedure (SP) that performs several UPDATEs against (potentially) all invoices OLDER than a...
14
by: Forgemaster66 | last post by:
Hi. My first post on the forum and I hope someone can help with this problem. I have a table called 'Inventory'. It has 11 fields, two of them being 'Num' and 'Plant'. There are a number of 'Plants'...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.