B. Gibson B. Gibson - 4 months ago 9
SQL Question

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.

Answer

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;