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?
Thank you.
6 6230
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?
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: -
insert into table1 (field1)
-
(select t2.field2
-
from table2 t2, table1
-
where t2.field3="string" and t2.field4=table1.field4)
-
I hope this solves your problem.
- Pradeep
Hi faif and pradeep kaltari,
Kindly use appropriate CODE tags when posting source code.
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: -
insert into table1 (field1)
-
(select t2.field2
-
from table2 t2, table1
-
where t2.field3="string" and t2.field4=table1.field4)
-
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.
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: -
UPDATE table1 SET field1 = (SELECT t2.field2 from table2 t2 WHERE t2.field3="string" AND t2.field4=table1.field4)
-
Then you need to use UPDATE insted of INSERT.
Try below code: -
UPDATE table1 SET field1 = (SELECT t2.field2 from table2 t2 WHERE t2.field3="string" AND t2.field4=table1.field4)
-
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.
Finally, a two-step solution has been found. The solution is: -
CREATE TEMPORARY TABLE temp
-
(select t2.field2, t2.field3
-
from table2 t2
-
where t2.field3="string");
-
-
update table1 t1, temp t
-
set t1.field1=t.field3
-
where t1.field2=t.field2;
-
Thank you all for the help.
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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"...
|
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 :...
|
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 ,
...
|
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...
|
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...
|
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...
|
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'...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
| |