Create a PK By Concat Two Table Columns

[StupidRalph]

I have a third party table that has 157,282 rows with no primary key which I will need for my webapp. So I want to create a unique value by concatenating the values of two columns and insert them in the newly added 'id' column.

INSERT INTO lieb_linkbase (id) (SELECT CONCAT(PUBLISHER,'-',NUMBER) FROM lieb_linkbase);

I just ran this query and it returned: "(157282 row(s)affected)
(0 ms taken)" which is the total amount of rows in my table. And it actually took longer than stated. However, when I went to check my dataset I seen that the id column was still NULL.

So I decided to run this from the command line to see what will happen and recieved this


080205 22:05:03 [ERROR] G:\xampp\mysql\bin\mysqld-nt.exe: Incorrect key file for table 'G:\xampp\tmp\#sql_2b4_0.MYI'; try to repair it

So a bit of Googling suggest that it had to do with available space of the tmp directory. So I moved it and ran the query a third time. This time it said...
Query OK, 629128 rows affected (3 min 42.21 sec)
Records: 629128 Duplicates: 0 Warnings: 0
And I still do not have the values inserted into my ID field...what gives? And why were so many rows affected!?! :confused:

[StupidRalph]

I may end up doing this in PHP. I was just trying to brush up on my SQL.

[Brandoe85]

You want UPDATE instead of INSERT if im understanding you correctly. Otherwise you want to add these additional rows to your table as you have done?

[StupidRalph]

:o
Awee c'mon Brandoe, it was 4 in the morning. Actually it wasn't, but I still should have went to sleep--apparently. WoW I don't believe I was trying an insert. Guess it was one of those moments.

[ralph l mayo]

Seems to me you just need a composite key rather than a denormalized column


ALTER TABLE lieb_linkbase ADD PRIMARY KEY (publisher, number);

[StupidRalph]

You would be correct Ralph. Why did I make this so difficult? I make your name look so bad sometimes. lol

:o x 2

[StupidRalph]

I was wrong in thinking I could use a composite primary key. Now that I'm back working on it, I remembered the reason I didn't use a composite primary key is b/c I was going to use the `id` as the SKU for each product.


Also, I can't use SELECT in the subquery of my UPDATE query b/c its the same table.

[ralph l mayo]

mysql 5? if so how about a view? I really think the denormalized column is a bad idea.


CREATE VIEW linkbase_view AS SELECT *, publisher || '-' || number AS id FROM lieb_linkbase;

[StupidRalph]

I think that may work. I'm not familiar with using views. I remember recently seeing an example that you used for postgreSQL using views that made me want to look into it. I'll read up on how to use them.