Select and Insert Query in One Query?

Question

Accounts Table

 user_id   description    credit  debit    created      modified
    01        Earned        10      0    06-11-2013     06-11-2013 
    02        Normal        0       1    05-11-2013     05-11-2013      
    03        Earned        5       0    04-11-2013     04-11-2013  
    04        Earned        20      0    03-11-2013     03-11-2013      
    05        Normal        0       1    02-11-2013     02-11-2013  
    01        Earned        5       0    02-11-2013     02-11-2013
    01        Normal        0       1    01-11-2013     01-11-2013
    02        Earned        10      0    01-11-2013     01-11-2013

I tried following Queries:

SELECT SUM(CASE WHEN description LIKE  '%Earned%'
THEN Credit
ELSE 0 
END ) as cre, SUM(debit) as deb FROM accounts
WHERE created is between '$Monday' AND '$Sunday'

INSERT into accounts (user_id, description, credit, debit, created, modified) 
VALUES ( 'user_id', 'Removed Free Earned', 'cre', 'deb', Now(), Now());

I want to Select Account table and Sum credit and Sum debit according to users for example if user_id 01 Total credit is 10 and Total debit in last Monday to Sunday is 3 then 7 points should be added in Insert Query place of 'deb'. But If Total credit is 10 and total debit is 20, then no points will be added mean if debit is greater than credit.

I want Select and Insert Query should be written as one query.

You cannot perform a SELECT and an INSERT in a single operation but consider providing proper DDLs and/or an sqlfiddle of same, and we'll see if we can help at all. — Strawberry
– Strawberry, Commented Nov 8, 2013 at 9:48

Deepika Janiyani · Accepted Answer · 2013-11-08 10:37:45Z

2

You can use the following query

INSERT into newaccounts (user_id, description, credit, debit, created, modified) 
SELECT user_id,description,
SUM(CASE WHEN 
description = 'Earned' 
THEN credit 
ELSE 0 
END) cre,
SUM(debit) as deb,NOW(),NOW() 
FROM accounts 
WHERE created between '03-11-2013' AND '04-11-2013';

Demo at http://sqlfiddle.com/#!2/da06e/1

answered Nov 8, 2013 at 10:37

Deepika Janiyani

1,4679 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

marekful · Accepted Answer · 2013-11-08 09:58:36Z

0

You can use MySQL's insert select syntax.

INSERT INTO accounts (col1, col2) SELECT col1, (expr) AS col2 FROM accounts

answered Nov 8, 2013 at 9:58

marekful

15.4k6 gold badges39 silver badges63 bronze badges

Collectives™ on Stack Overflow

Select and Insert Query in One Query?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related