Click here to Skip to main content
15,867,488 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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. 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.

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.

What I have 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);


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
Posted
Updated 30-Jan-22 10:06am
v11
Comments
OriginalGriff 29-Jan-22 0:38am    
This is not a good question - we cannot work out from that little what you are trying to do.
Remember that we can't see your screen, access your HDD, or read your mind - we only get exactly what you type to work with - we get no other context for your project.
Imagine this: you go for a drive in the country, but you have a problem with the car. You call the garage, say "it broke" and turn off your phone. How long will you be waiting before the garage arrives with the right bits and tools to fix the car given they don't know what make or model it is, who you are, what happened when it all went wrong, or even where you are?

That's what you've done here. So stop typing as little as possible and try explaining things to people who have no way to access your project!
So we don't really know what exactly you are trying to do, and have no idea of your tables or the data they hold. And we need that to work out what is wrong with a query!

So show us the table layout: the columns each holds, and tell us how they are related. Give us sample "before" and "after" data, and show us what you wanted to happen - which needs both rows that are updated and those that aren't.

Use the "Improve question" widget to edit your question and provide better information.
Smoka 2022 29-Jan-22 10:21am    
You have made valid criticism, I hope this is enough to go from.

1 solution

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';

courtesy to @MBuschi from dba.stackexchange
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900