CODEWITHSUNDEEP
sqlstored-proceduresinsert
harqs
harqs

Reputation: 177

Insert query values from select query

i want to set the value to a insert query from select query ,how to perform this.

insert into t_user (id,user_id) values (1, ) 
where user_id in (select id from user where id=123)

I want to set the value of user_id from select query value ,how to do this in sql?

Upvotes: 3

Views: 1078

Answers (6)

jayu
jayu

Reputation: 337

INSERT INTO t_user(id, user_id)
       SELECT id,1
       FROM user ;
       WHERE id = 123

Upvotes: 1

user1416258
user1416258

Reputation:

Assuming you have a table structure like this

----------      -------
 articles        users
----------      -------
 rowid           rowid
 article_id      user_id
 user_id         user_name

And a few entries like this

---------------------------------
 users
---------------------------------
 rowid     user_id     user_name
---------------------------------
   1         124         joe
   7         12309       mark

The correct way to insert a value based on a lookup / reference from another table would be this:

 INSERT INTO articles (rowid, article_id, user_id)
     SELECT 1, 12392, user_id FROM users
     WHERE rowid = 7

This would insert the following record into articles

---------------------------------
 articles
---------------------------------
 rowid     article_id    user_id 
---------------------------------
   1          12392       12309

It is beyond me, however, why you would use either this database design or insert method.

Upvotes: 0

Nighil
Nighil

Reputation: 4127

insert into t_user (id,user_id)
select 1,id from user where id=123

if your table has only two columns then no need to specify columns again 

  insert into t_user
  select 1,id from user where id=123

Upvotes: 2

SPIRiT_1984
SPIRiT_1984

Reputation: 2787

And in that case, what's the problem with this:

INSERT INTO t_user(id, user_id) VALUES(1,123);

I mean the problem is a little bit strange, if you know both values from the start, why bother with select at all.

Upvotes: 1

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79969

Try this:

INSERT INTO t_user (id, user_id)
SELECT 1, id 
FROM user
WHERE id = 123

Upvotes: 3

psur
psur

Reputation: 4519

insert into t_user(id, user_id) select 1, id from user where id=123;

Upvotes: 2

Related Questions