CODEWITHSUNDEEP
sqlsql-server
Crane
Crane

Reputation: 15

UPDATE SQL column based on values of another column. (It's not that simple)

I am trying to update a column with a value of a different column under certain circumstances. It's complicated. I've tried to figure this out on my own for a weeks now. (on and off)

Here is how it currently looks: enter image description here

I Need a SQL statement that will look for the LotNo at OperationCode 1280 and assign it for all values with the same CastTID.

Here is what it should look like after enter image description here

I would really appreciate any help! This is my first post, if iv'e left out anything important please let me know so that I can help you help me.

Upvotes: 1

Views: 86

Answers (3)

Becuzz
Becuzz

Reputation: 6857

You can use a join in your update statement to make this easier.

update t
set CastLotNo = t2.LotNo
from yourTable t
inner join yourTable t2 on t2.CastTID = t.CastTID and t2.OperationCode = 1280

Upvotes: 1

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48207

WITH cte as (
    SELECT CastTID, LotNo
    FROM Table1
    WHERE OperationCode = 1280
)
UPDATE myTable T
SET CastLotNo = (SELECT C.LotNo as CastLotNo 
                 FROM cte C
                 WHERE C.CastTID = T.CastTID)

Upvotes: 0

CollinD
CollinD

Reputation: 7573

UPDATE `myTable` SET `CastLotNo` = (SELECT `LotNo` FROM `MyTable` WHERE `OperationCode` = '1280' LIMIT 1) WHERE `CastTID` = (SELECT `CastTID` FROM `MyTable` WHERE `OperationCode` = '1280' LIMIT 1)

It seems like you could use something like that. I can't test this at the moment, so let me know if it needs tweaking.

Upvotes: 0

Related Questions