How do I fix this SQL server query 2008r2

Question

I'm trying to update type from table Titem column type and set it to new id, while having it to check if user id owns those 2 items(oldid which we are updating, 2nd id which is just for checking if user_id owns it). However that is the part that I'm doing wrong I don't know how to use 1 of the item types as it is just while updating the other one since they both share type as reference. I know that im using like someone would say antique joins, that is besides the point my goal is make it work not optimisation, I'm not a developer, nor any type of coder. I'm a newbie with somewhat basic understanding nothing more. Since everything is cleared now I'd appreciate if someone would give me an example of this working or how to make it work. It works until the Exist part which is pretty much ignored. The queue gets executed fully, however like I said last and gets ignored. There are no msgs: And that's what pretty much works/gets executed:

UPDATE TItem
SET type= 'newid' 
FROM Titem x, TCharacter y
WHERE x.char_id = y.id
and x.type = 'oldid'

table1 'tcharacter'

| id | create date | mode |

Table2 'titem'

| type | Attr | char_ID |

id=char_id That's the only column they share the same data Goal: IF USER_ID has 3rditem id the queue should get executed -> updating old id to newid. That's one way of me explaining it.What I've tried down below is me trying to update the ID while checking user_id has the 2nd itemid present. What I've tried:

UPDATE TItem
SET type= 'newid' 
FROM Titem x, TCharacter y
WHERE x.char_id = y.id
and x.type = 'oldid'
and EXISTS
(SELECT type = '2nd id'  FROM Titem WHERE x.char_id = y.id);

The new IF query that I've tried is beyond criticism how messy it got, the issue with is that regardless if the item exists or not its irrelevant as long its present in the db once, which results in the queue to be executed for every record adding all the x and y specifications resulted in nothing obviously Im doing it wrong.

IF EXISTS (SELECT 1 FROM Titem as x, TCharacter as y WHERE x.type = '2nd id' AND x.char_id = y.id) 

 BEGIN
     
     UPDATE Titem
SET type =  'mewid'
FROM dbo.TItem AS x, TCharacter AS y
WHERE x.char_id = y.id
and x.type = 'oldid'

     
     
 END
 

Answer 1

I'm not sure to understand your problem; please try this one:

UPDATE x
SET x.type= 'newid' 
FROM Titem AS x
INNER JOIN TCharacter AS y ON x.char_id = y.id
INNER JOIN Titem AS z ON z.char_id = y.id
WHERE x.type = 'oldid'
and z.type = '2nd id';

dbfiddle

maybe you can add some data to dbfiddle...