Table1

HI Guys, what SQL query would update Decision column if duplicate has been found in Code column.

other words : ID 1 with Code TK01 has "Yes" so ID 3 should be updated with "Yes" value in Decision column.

Same as ID 5 as it matches Code with ID 4 and ID 4 has "Yes" value in column Decision.

I have created that in Access(code below) but how to do it in t-sql or mysql please

UPDATE Table1 t1 INNER JOIN Table1 t2
ON t1.Code = t2.Code
SET t1.Decision  = t2.Decision

CodePudding user response：

In T-SQL I would do this using an updateable CTE. Using analytic functions, get the correct value of decision and identify code with duplicates, then update just those that are duplicates:

with u as (
    select *,
      Max(decision) over(partition by code) v, 
      Count(*) over(partition by code) cnt
    from t
)
update u
set decision = v
where cnt > 1 and decision = '---'

CodePudding user response：

tsql:

UPDATE t1 SET t1.Decision = 'Yes' FROM Table1 t1 INNER JOIN Table1 t2 ON t1.Code = t2.Code
WHERE t1.Decision <> t2.Decision

You can watch expected result without executing it by adding UPDATE statement in block

BEGIN TRAN
SELECT * FROM Table1

UPDATE t1 SET t1.Decision = 1 FROM Table1 t1 
INNER JOIN Table1 t2 ON t1.Code = t2.Code
WHERE t1.Decision <> t2.Decision

SELECT * FROM Table1

ROLLBACK TRAN

CodePudding user response：

This should work:

UPDATE a
SET decision = 'Yes'
FROM table1 AS a
   INNER JOIN table1 AS b
       ON a.code = b.code
          AND (a.decision = 'Yes' OR b.decision = 'Yes');  

You should normalise your table though. If the decision depends on the code only, you should consider moving the decision column to a new table, so you'd have two tables U(ID, Code) and V(Code, Decision).