CODEWITHSUNDEEP
sqlsql-server
B. Gibson
B. Gibson

Reputation: 13

Update a field with data from another key

I run a tool room where we use a database to track our tools. Items can be nested under other items by entering the ItemID of the "parent" tool in the "ParentID" column. For example, a wrench sits inside a tool box, so the ItemID of the tool box (2) is entered in the ParentID field for the wrench. Here is an example table:

ItemID  BarcodeID   Name      ParentId   UserTxtBox
1       0001        Wrench    2          null
2       box1        Tool Box  null       null
3       0002        Socket    4          null
4       box2        Tool Box  null       null

I'm trying to set the value of UsrTxtBox to the BarcodeID of it's parent item, as shown here:

ItemID  BarcodeID   Name      ParentId   UserTxtBox
1       0001        Wrench    2          box1
2       box1        Tool Box  null       null
3       0002        Socket    4          box2
4       box2        Tool Box  null       null

The query that I've come up with, but isn't working is:

DECLARE @parentid int
SET @parentid = null

UPDATE dbo.items
SET @parentid = ParentId
    ,UserTxtBox = (SELECT BarcodeId FROM dbo.items WHERE ItemId = @parentID)

The query runs without errors, but makes no changes. During the course of my experimentation, if i substitute "2" for @parentid in the where clause, it does "work", but of course it would make UserTxtBox = box1 for every item in the table. If instead, i set @parentid = 2, and leave the variable name in the where clause, it still does not work. I have verified that the value of ParentId is getting stored in @parentid, but when I use the variable in the where clause it doesn't work. Why not?

The ItemID is the primary key, and ParentID is a foreign key. Are keys treated differently from plain integers in these statements?

Thanks in advance for any assistance. After years of looking up answers on this site, it still amazes me that so many people give their time to share their knowledge.

Upvotes: 1

Views: 25

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270061

How about an update with a join:

update i
    set UserTxtBox = p.BarcodeID
    from dbo.items i join
         dbo.items p
         on i.ParentId = p.ItemId;

Upvotes: 1

Related Questions