Mysql insert into

[jodyschwartz]

I have an insert query that is running with a select subquery. This needs to basically “fork off” copies of the rows it finds and inserts. This part works but there is no garbage collection, even if this was done it still creates blank records. I have this running on a database event so it finds records based on the criteria then forks it off as new records… runs again fork this off. I tried this with if not exists but something is off still.

INSERT INTO mydonations (pid, itemname, pay_email, paymentdate, firstname, lastname, paymentstatus, stdate, donator_id, serialize, gross_price)
SELECT myd.pid, myd.itemname, myd.pay_email, myd.paymentdate, myd.firstname, myd.lastname, myd.paymentstatus, myd.stdate, myd.donator_id, 'REPO', SUM(myd.gross_price * -1) 
FROM mydonations AS myd, crowd_entries AS ent
WHERE NOT EXISTS (SELECT serialize, pid FROM mydonations AS myd WHERE serialize = 'CAR' AND myd.pid = myd.pid)
AND myd.pid = ent.id 
AND myd.paymentstatus = 'Pending' 
AND myd.serialize = 'CAR' 
AND ent.total > (SELECT sum(gross_price) FROM mydonations WHERE myd.serialize = 'CAR')

[r937]

@jodyschwartz

but something is off still

not familiar with that particular error message

please say again what kind of help you’re looking for

[r937]

by the way, you have SUM() in the SELECT clause but you’re missing the GROUP BY clause

also, the tables in the FROM clause are not joined properly (unless you intended to have a cross join, in which case CROSS JOIN is the preferred syntax)

also, myd.pid = myd.pid in the subquery isn’t doing what you think it’s doing

[jodyschwartz]

Thanks, I didn’t give an error, the problem is this runs as a database event and when it first runs it creates the expected record but every time this runs it creates a blank record. It should not create any new records after the first run. I just need one record created initially (to offset what was found) then no other record when this runs every interval. I will apply the suggestions.