Reputation: 4192
As my application is expanding, I now am changing the structure of my database; I now want to control file types within the database. I wanted to start with the current file types already in the database. My Database now has a [simplified] 2 table structure like:
tbFiles: pkFileID, fileType, fileName
tblFileType: pkFileType, typeName, typeDesc
I am trying to have the output of a SELECT
query update into the newly created tblFileType
table. I have tried among other things:
UPDATE tblFileType
INNER JOIN
(SELECT DISTINCT fileType FROM tblFiles) as X
SET typeName = fileType
but I always seem to get 0 row(s) affected
.
When I run
SELECT DISTINCT fileType
FROM `tblFiles`
I get Showing rows 0 - 22 (~23 total, Query took 0.0074 sec)
I know this must be simple, but why is the UPDATE query not affecting 23 rows?
Upvotes: 0
Views: 347
Reputation: 16673
you just want to populate the table - not update anything in there (especially since nothing exists yet)
INSERT INTO tblFileType(typeName )
SELECT DISTINCT fileType FROM tblFiles
Upvotes: 1
Reputation: 79979
You need to add a JOIN
condition like ON t1.fileType = x.fileType
as follows:
UPDATE tblFileType t1
INNER JOIN
(
SELECT DISTINCT fileType
FROM tblFiles
)as X ON t1.fileType = x.fileType
SET t1.typeName = X.fileType
Update: Since the table tblFileType
is blank, you will need to use INSERT
something like:
INSERT INTO tblFileType(typeName )
SELECT DISTINCT fileType
FROM tblFiles
WHERE -- a condition here
Upvotes: 2