Click here to Skip to main content
14,981,116 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I'm trying to insert value form temp table with multiple query but can't achieve.
DROP TEMPORARY TABLE IF EXISTS temp; CREATE TEMPORARY TABLE IF NOT EXISTS temp SELECT s.roll, s.name, s.code, f.amount FROM fee f INNER JOIN students s ON s.code = f.code WHERE NOT EXISTS (SELECT 1 FROM fee f1 WHERE f1.code = f.code AND STR_TO_DATE(f1.datenewfee, '%d-%M-%Y') > STR_TO_DATE(f.datenewfee, '%d-%M-%Y')) AND s.status = 1 AND s.roll <> '' AND s.institution = 'tec'; SELECT t.roll, t.name, t.code, t.amount - IFNULL(d.amount, 0) as amount, 'tec', 'kahhar' FROM discount d RIGHT JOIN temp t ON t.roll = d.roll GROUP BY t.roll;


What I have tried:

INSERT INTO receivable (roll, name, month, code, amount, institution, created_by); 
DROP TEMPORARY TABLE IF EXISTS temp; 
CREATE TEMPORARY TABLE IF NOT EXISTS temp 

SELECT s.roll, s.name, s.code, f.amount 
  FROM fee f INNER JOIN students s ON s.code = f.code 
  WHERE NOT EXISTS (SELECT 1 FROM fee f1 WHERE f1.code = f.code AND 
  STR_TO_DATE(f1.datenewfee, '%d-%M-%Y') > STR_TO_DATE(f.datenewfee, '%d-%M-%Y')) 
  AND s.status = 1 AND s.roll <> '' AND s.institution = 'tec'; 

SELECT t.roll, t.name, t.code, t.amount - IFNULL(d.amount, 0) as amount, 'tec', 'kahhar' 
  FROM discount d RIGHT JOIN temp t ON t.roll = d.roll GROUP BY t.roll;
Posted
Updated 6-Nov-19 20:15pm
v3
Comments
MadMyche 6-Nov-19 17:22pm
   
May I suggest formatting your code, and also adding in what happens when you try to execute this

1 solution

The first statement is missing values:
INSERT INTO receivable (roll, name, month, ...); 

It should be something like:
INSERT INTO receivable (roll, name, month, ...) VALUES (value1, value2, value3, ...); 

For more information see: SQL INSERT INTO Statement[^]
I recommend doing some practicing by using the 'Try it yourself' button.

But what you probably want to use is the: SQL INSERT INTO SELECT Statement[^]
   
v2

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900