Click here to Skip to main content
11,495,688 members (69,592 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL MySQL
I have two tables one is account table and the another is missinginfo table.

I want to insert the id column from the account table to the missing info table..


here is my query

 insert into missinginfo ( mfname,mlname,id) values ('john','wilson', 'select id from account where username=abc@abc.com')

Query gives no error but the id column give wrong result i.e 0 ..

where is the problem..
Posted 10-Jan-14 3:01am
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

insert into missinginfo ( mfname,mlname,id) values ('john','wilson', (select id from account where username='abc@abc.com'))
  Permalink  
v2
Comments
idle63 at 10-Jan-14 11:51am
   
The correct answer. 5 from my side.
KARTHIK Bangalore at 10-Jan-14 12:07pm
   
Thanks a lot:)
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Your insert query is wrong.
Check below tsql query.

insert into missinginfo ( mfname,mlname,id) select 'john','wilson',id from account where username='abc@abc.com'
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Remove the single quotes from the id-sql

like this
insert into missinginfo ( mfname,mlname,id) values ('john','wilson', select id from account where username=abc@abc.com)


[EDIT]correction after reading solution #1 from KARTHIK[EDIT]
insert into missinginfo ( mfname,mlname,id) values ('john','wilson', (select id from account where username=abc@abc.com))


and not like
 insert into missinginfo ( mfname,mlname,id) values ('john','wilson', 'select id from account where username=abc@abc.com')
  Permalink  
v3
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

Use this

insert into missinginfo ( mfname,mlname,id) 
select 'john','wilson',id from account where username='abc@abc.com'

and if you want to insert multiple records then use this.
insert into missinginfo ( mfname,mlname,id) 
select 'john','wilson',id from account where username='abc@abc.com'
union all
select 'john2','wilson2',id from account where username='abc@abc.com'
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 5

You have wrong query there in id section in values.

Putting single quotes around your select statement makes it static string it wouldn't be executed.

Removing those single quote around your select statement will be executed.
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Dnyaneshwar@Pune 692
1 CHill60 318
2 Sascha Lefèvre 275
3 Nakul Chaudhari 236
4 RyanDev 220
0 Sergey Alexandrovich Kryukov 10,362
1 OriginalGriff 8,821
2 Sascha Lefèvre 3,899
3 Maciej Los 3,422
4 Richard Deeming 2,600


Advertise | Privacy | Mobile
Web04 | 2.8.150520.1 | Last Updated 10 Jan 2014
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100