[RESOLVED] Difficult nested SELECT within an INSERT statement

[eyeRmonkey]

From everything I've read, I don't think this is possible, but here is the statement I am trying to achieve:

My columns: primary_key, name, name_based_id

INSERT IGNORE INTO artists (primary_key, name, name_based_id) VALUES 
('10:w9ftxqe5ldhe', 'Joe', (SELECT COUNT(*) FROM artists WHERE name='Joe')), 
('11:jifuxqr5ldh', 'Bob', (SELECT COUNT(*) FROM artists WHERE name='Bob')),
('22:a9ftx34gldae', 'Joe', (SELECT COUNT(*) FROM artists WHERE name='Joe')), 
...

Basically, every time I add an artist, I want the name_based_id to be equal to the number of artists already present in the database with that name.

It would result in something like so:
'10:w9ftxqe5ldhe', 'Joe', 0
'11:jifuxqr5ldh', 'Bob', 0
'22:a9ftx34gldae', 'Joe', 1

The third row is "Joe 1" because he was the second joe added to the database.

From what I've read, you can't do a nested SELECT from the same table you're inserting into.

Is there anyone to accomplish this in one query?

I'd even settle for multiple queries, as long as it got the job done.

If I didn't clarify it enough, please let me know. I really need some assistance on this.

Thanks.

Note: I'm using MySQL 5.0. I'm working with an InnoDB engine. I'm writing this all in PHP, but I don't think that matters for this question.

[brucevde]

No idea if MySQL supports this type of query but since it works in SQL Server I thought it probably would.

INSERT INTO artists (primary_key, name, name_based_id)
Select * From
   (Select '10:w9ftxqe5ldhe' as primary_key, 'Joe' As [name], name_based_id=(SELECT COUNT(*) FROM artists WHERE name='Joe')
    Union All
    Select '11:jifuxqr5ldh', 'Bob', (SELECT COUNT(*) FROM artists WHERE name='Bob')
    Union All
    Select '22:a9ftx34gldae', 'Joe', (SELECT COUNT(*) FROM artists WHERE name='Joe')
   ) As Data

[eyeRmonkey]

I can't get it to stop having syntax errors. The first error I get is:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[name], name_based_id=(SELECT COUNT(*) FROM artists WHERE name='Joe')
Union' at line 3

When I remove the square brackets from NAME, I then get this error:

#1054 - Unknown column 'name_based_id' in 'field list'

Any ideas?

[si_the_geek]

To find out what the issue is (I presume a MySQL syntax issue, possibly due to "name_based_id="), try simplified versions, eg:

INSERT INTO artists (primary_key, name, name_based_id)
Select * From
   (Select '10:w9ftxqe5ldhe' as primary_key, 'Joe' As [name], (SELECT COUNT(*) FROM artists WHERE name='Joe') as name_based_id
   ) As Data

..if that doesn't work, try this one:

INSERT INTO artists (primary_key, name, name_based_id)
Select '10:w9ftxqe5ldhe' as primary_key, 'Joe' As [name], (SELECT COUNT(*) FROM artists WHERE name='Joe') as name_based_id

[eyeRmonkey]

I still get errors about the square brackets on both of those.

[eyeRmonkey]

Doing this query:

INSERT INTO artists (primary_key, name, name_based_id)
Select * From
   (Select '10:w9ftxqe5ldhe' as primary_key, 'Joe' As name, (SELECT COUNT(*) FROM artists WHERE name='Joe') as name_based_id)

I get this error: #1248 - Every derived table must have its own alias

I'm clueless... I'm just trying random things to get it to work.

UPDATE:

I think you had an extra SELECT in your SQL, si.

I got this working, now I need to build it up to have all the other two rows in the query.

INSERT INTO artists (primary_key, name, name_based_id)
Select '10:w9ftxqe5ldhe' as primary_key, 'Joe' As name, (SELECT COUNT(*) FROM artists WHERE name='Joe') as name_based_id

Oh... I just noticed that is what your second query was. Jeeze. Sorry.

[eyeRmonkey]

Here is what I came up with to do the muti-row insert:

INSERT IGNORE INTO artists (primary_key, name, name_based_id)
Select '10:w9ftxqe5ldhe' as primary_key, 'Joe' As name, (SELECT COUNT(*) FROM artists WHERE name='Joe') as name_based_id Union All
Select '11:jifuxqr5ldh', 'Bob', (SELECT COUNT(*) FROM artists WHERE name='Bob') Union All
Select '22:a9ftx34gldae', 'Joe', (SELECT COUNT(*) FROM artists WHERE name='Joe')

The problem is, that the name_based_id for the SECOND 'Joe' is still 0. I think that's because the first Joe hasn't been inserted before the nested SELECT COUNT() is evaluated.

If I add the third row in a separate query from the first two queries, then it is 1 (as it should be). Basically, the only way it works right now is if I never add two people with the same name in a single query.

So I guess I'm half way to where I want to be. Does anyone have any idea on how I could add two 'Joe's in one query and still have the name_based_id column be accurate?

[si_the_geek]

I don't think there is a way of doing that in a single statement, I think you'll need to run two separate ones.

[eyeRmonkey]

Dang. Alright. I guess I'll just do some checking before I execute the query to ensure that no artist name appears twice.

I'm going to leave this unresolved for a day or so to see if anyone else can think of a way to accomplish that (szlamany or bruce, maybe?).

Thanks for your help, si.

[techgnome]

Question - why are you trying to do it this way?

What if the first Joe is then deleted and you insert another one... then you have two Joes, both with 1 for the field.....

-tg

[eyeRmonkey]

I guess I didn't mention that, but the first Joe will never be deleted.

[brucevde]

I'm going to leave this unresolved for a day or so to see if anyone else can think of a way to accomplish that (szlamany or bruce, maybe?).

Sorry I can't be of more help as I don't know MySQL. The Insert statement works in SQL Server (including the select count subqueries) so obviously it processes things differently.

[techgnome]

It's because the inserts are batched up.... not processed row by row.

I have a feeling you are going to have to do it one record at a time....

-tg

edit: you still never mentioned why you are doing it this way... what's the purpose/significance of the number?

[eyeRmonkey]

Later on when I use this data, I need to be able a unique identifier based on the name. Therefore, if there are three people with the name 'Joe' in the database, I need to able to make a unique identifier like:

'Joe_1'
'Joe_2'
etc....