Update Query using SELECT results

Question

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?

Mahmoud Gamal · Accepted Answer · 2012-10-08 15:01:33Z

2

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

edited Oct 8, 2012 at 15:01

answered Oct 8, 2012 at 14:48

Mahmoud Gamal

80.2k18 gold badges143 silver badges168 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sablefoste Over a year ago

Thank you for the quick reply. I originally had an ON clause of 1=1, but it didn't do anything. Your query as shown has an ON clause, but the table tblFileType is blank, so there is nothing to match records with the ON. When making small corrections to the Query above (t1.fileType -> t1.typeName, x.fileType -> X.fileType, ) , I still get 0 Records returned.

Mahmoud Gamal Over a year ago

@SableFoste You can use INSERT INTO ... SELECT see my edit.

Sablefoste Over a year ago

thank you very much, that was it! I knew it was obvious; sometimes I just get a 'duh' moment!

Randy · Accepted Answer · 2012-10-08 15:07:33Z

1

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

answered Oct 8, 2012 at 15:07

Randy

16.7k2 gold badges40 silver badges56 bronze badges

Collectives™ on Stack Overflow

Update Query using SELECT results

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related