Click here to Skip to main content
14,388,435 members
Rate this:
Please Sign up or sign in to 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 21: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

Rate this:
Please Sign up or sign in to vote.

Solution 1

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, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100