0

I have created 3 tables: playlist, tracklist, & xmlexport, which I use for storing my playlists. To keep from duplicating any of the items in the playlist, I use a tracklist that I insert from the playlist & I insert the top row of my tracklist into my xmlexport which I then export the track to be played in the form of an .xml file to be read by my player. Each time this process happens I remove a row from the tracklist, so when the table plays the last item it becomes empty & I need to run a query to update the tracklist table from the playlist table. This is what I've been playing with but its not working properly.

       INSERT INTO dms.test_trk
       SELECT * FROM dms.test_ply
       WHERE row_count() <= 0;
Sagar Panda
  • 561
  • 3
  • 17
drs
  • 45
  • 1
  • 6
  • 1
    Not sure if I have understood your question correctly, but if I have it sounds like what you need is a queue and not a database. Take a look at redis lpush and rpop for example. – e4c5 Oct 06 '15 at 06:43

2 Answers2

1

With your statement

 INSERT INTO dms.test_trk
 SELECT * FROM dms.test_ply
 WHERE row_count() <= 0;

what exactly do you expect to happen? I would have thought that the result from the last operation (i.e. the one before executing this statement) would be compared with zero and accordingly rows are retrieved from test_ply or not.

You, however, seem to think that row_count somehow tells you whether there are records in test_trk.

Both assumptions seem wrong. My testing indicates that row_count is reset to -1 when starting an INSERT, so you cannot use this function inside an INSERT statement at all.

What you are looking for instead is a simple EXISTS clause:

insert into test_trk
select * from test_ply where not exists (select * from test_trk);
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • when i run "where not exists" it returns 0 rows affected. I have this working in ms access with stored function ( if rst.recordcount <=0 then run query) i'm just trying to find a way to accomplish this with mysql. do i need to create a function or is there a simple sql statement that will work? – drs Oct 06 '15 at 15:40
  • All this statement does is refill test_trk in case it's empty. You don't have to do anything beside it; you don't have to additionally count the records. As long as there is at least one entry in test_trk this statement does nothing, as soon as test_trk is completely empty the statement refills the table. – Thorsten Kettner Oct 07 '15 at 07:18
  • "where not exists" was not working for me, but i tried it again today and yes!! its working!! so i really appreciate your time to look at this. – drs Oct 08 '15 at 20:17
0

I think you need a stored procedure that checks if count(*) in test_trk is 0. If so, run:

 INSERT INTO dms.test_trk
 SELECT * FROM dms.test_ply

This and this should get you on the right track.

Community
  • 1
  • 1
Mihai Ovidiu Drăgoi
  • 1,307
  • 1
  • 10
  • 16