Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
SELECT a.nid, a.stock, b.qty, sum( b.qty )
FROM uc_product_stock a
INNER JOIN uc_order_products b ON a.nid = b.nid
GROUP BY a.nid

the above query give me the following result:

nid stock   qty sum(b.qty)
10  746     1   4
11  256     4   11

now i want to insert this in my custom table. For eg, the structure of custom table is:

id | orderIn | orderOut
10  |   746   |   4
11  |   256   |   11

Iam using this query, but it gives error:

insert into custom (id, OrderIn, OrderOut) 
select 
a.nid, 
a.stock,
b.qty,
sum(b.qty)
from 
table1 a inner join table2 b on a.id=b.id
group by a.id
share|improve this question

closed as off topic by Jack Douglas Apr 19 '13 at 14:26

Questions on Database Administrators Stack Exchange are expected to relate to database administration within the scope defined by the community. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about reopening questions here.If this question can be reworded to fit the rules in the help center, please edit the question.

    
How are id and sku related? Is sku allways 'sku-'+id ? –  Stuart Moore Feb 4 '13 at 11:55
    
no there is no relation in sku(varchar) and id(int):( –  Aashi Feb 4 '13 at 11:59
    
BTW, I made a mistake in the previous note, and meant is SKU always the string 'prod-' with the id appended. If there's no relationship, then this query logically can't exist as there's no way to like a specific sku to a particular id. –  Stuart Moore Feb 4 '13 at 12:03
    
@Stuart Moore table 2 i took first was not the right choise. now i had edited the question. Pls have a look –  Aashi Feb 4 '13 at 12:20
    
INSERT into order (sku, orderIn, orderOut) SELECT a.nid, a.stock, b.qty from uc_product_stock a inner join uc_order_products b on a.nid=b.nid. Whats wrong with the syntax of this mysql query. this query gives error –  Aashi Feb 4 '13 at 12:56
show 3 more comments

2 Answers 2

You'd use an INSERT..SELECT query like this:

insert into custom (sku, OrderIn, OrderOut) 
select 
a.id, 
a.stock,
sum(b.prod_order)
from 
table1 a inner join table2 b on a.id=b.id
group by a.id

More details at http://dev.mysql.com/doc/refman/5.1/en/insert-select.html

The basic idea is that you build up your select query to define the set of data you want, and then use that as an input to an Insert statement to write it into the table you want to put it in.

In your question you've not defined how the created column is generated, so that isn't covered in this query.

share|improve this answer
    
sorry for table 2 structure it does not have sku, instead it is id(i.e a primary key) –  Aashi Feb 4 '13 at 11:51
    
OK, I've edited my answer to incorporate your revisions, though please note that as the created column isn't defined anywhere I can't answer that section. –  Stuart Moore Feb 4 '13 at 14:56
    
select a.id, a.stock, b.prod_order, sum(b.prod_order) from table1 a inner join table2 b on a.id=b.id group by a.id. this is ok. But this - insert into custom (sku, OrderIn, OrderOut) select a.id, a.stock, b.prod_order, sum(b.prod_order) from table1 a inner join table2 b on a.id=b.id group by a.id gives error –  Aashi Feb 5 '13 at 3:48
    
it gives error that there is a syntax error near select –  Aashi Feb 5 '13 at 3:49
    
i had edit my que pls answer that one –  Aashi Feb 5 '13 at 3:50
show 1 more comment

Aashi, you are selecting 4 columns and trying to insert into only 3 columns of another table.

share|improve this answer
1  
This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post - you can always comment on your own posts, and once you have sufficient reputation you will be able to comment on any post. –  dezso Feb 5 '13 at 8:38
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.