How do I assign this concatenated value back to its table?

Question

I'm having beginner troubles with MySQL. I'm trying to build a DB of parts which have a ten digit alphanumeric part number based on the department and class of a part. For example, a part from department "Foo" of class "Bar" might be FOBAR10000. The departments and classes are stored in their own tables, so I store the IDs with the part instead of the strings "FO" and "BAR" in the parts table.

I've come up with a MySQL query to lookup and concatenate the full part name.

SELECT pl.idPartsList as 'resultid', concat(pd.DeptShortName, pc.ClassShortName,pl.PartIntID) as 'Fullname'
FROM partdepartments pd, partclasses pc, partslist pl
WHERE pl.PartDeptId = pd.idPartDepartments and pl.PartClassID = pc.idPartClasses;

Which generates:

resultid | Fullname
---------------------
1        | FOBAR10000

I want to assign this result back to the part under the column PartFullAlphaID, as follows, to avoid having to run this query often.

Before:

idPartslist | PartDeptID | PartClassID | PartIntID | PartFullAlphaID
---------------------------------------------------------------------
1           | 1          | 1           | 10000     |

After:

idPartslist | PartDeptID | PartClassID | PartIntID | PartFullAlphaID
---------------------------------------------------------------------
1           | 1          | 1           | 10000     | FOBAR10000

but the following doesn't work:

UPDATE test_db_1.partslist 
SET PartFullAlphaID = Fullname 
WHERE test_db_1.partslist.idPartsList = resultid;

Because: Error Code: 1054. Unknown column 'resultid' in 'where clause'

How can I assign this string value back to the part?

score 5 · Accepted Answer · edited May 23 '17 at 11:52

5

This is an update on joined tables.
Try this Query:

UPDATE partslist pl
    JOIN partdepartments pd ON pl.PartDeptId = pd.idPartDepartments
    JOIN partclasses pc ON pl.PartClassID = pc.idPartClasses
SET pl.PartFullAlphaID = concat(pd.DeptShortName, pc.ClassShortName, pl.PartIntID)

also look at stackoverflow.com/../how-to-use-join-in-update-query

edited May 23 '17 at 11:52

Community

1
1

answered Dec 17 '15 at 15:43

bw_üezi

4,483
4
23
41

score 0 · Answer 2 · answered Dec 17 '15 at 15:43

Have you tried selecting the results out into variables?

DECLARE @ResultID varchar(10),
        @FullName varchar(100)


SELECT  @ResultID = pl.idPartsList, 
        @FullName = concat(pd.DeptShortName, pc.ClassShortName,pl.PartIntID)
FROM    partdepartments pd, partclasses pc, partslist pl
WHERE   pl.PartDeptId = pd.idPartDepartments 
    AND pl.PartClassID = pc.idPartClasses;


UPDATE  test_db_1.partslist 
SET     PartFullAlphaID = @FullName 
WHERE   test_db_1.partslist.idPartsList = @ResultID;

I use MSSQL so the syntax may be (very slightly) different, but I use this syntax often when updating records.

score 0 · Answer 3 · answered Dec 17 '15 at 15:45

You can just a subquery that generates the concatenated value on the fly:

UPDATE test_db_1.partslist 
SET PartFullAlphaID = (
    SELECT concat(pd.DeptShortName, pc.ClassShortName, partslist.PartIntID)
    FROM partdepartments pd, partclasses pc
    WHERE partslist.PartDeptId = pd.idPartDepartments 
    and partslist.PartClassID = pc.idPartClasses
)
--add a filter if you only want to update a subset of the partslist table
--WHERE partslist.<criteria> = <whatever>

Good luck!

How do I assign this concatenated value back to its table?

3 Answers3