update records if condition met!

[TallSA]

Hi All,

I have the below table in my DB, I would like to be able to search if the the column Action = "Discard" then all the field in the Action for the same Number set to "Discard"

Number	RepeatNo	Count	Action
C001	5	4
C001	1
C001	2
C001	3		dis
C001	4
C001	6
C001	2	2
C002	1
C002	2		not
C002	3
C002	4
C002	5		dis
C002	6
C003	2	6
C003	5	1
C003	2	5
C003	2	6
C003	4	1
C004	1		not
C004	2		not
C004	3		not
C004	4		yes
C004	5		yes
C004	6
C005	4	3
C005	5	12	dis
C005	4	3	yes
C005	5	12
C005	2	1
C005	2	1
C005	2	1	dis
C006	1	1
C006	4	3
C006	1	1
C006	4	3
C006	1	1	dis
C006	4	3

Thanks in advance for your help.

[ranman256]

build query 1 (to get the list)
Q1 = select distinct [Number] from table where [action] = 'Discard'


then use query 1 to update records from query 2
Q2 = update Q1 set [action] = 'Discard'

or you may have to join Q1 to your table on [number] (in case Q2 above cant update records)

[TallSA]

Hi Ranman256,

Thank you for your help.

I couldn't update Q2, so I joined the Q1 o my table, but nothing happened.

Regards,

[nick404]

So if one of the multiple C001 is action = dis you want to set all the C001 's to dis?

Do a join query joining on Number and then an update to update the records of the table.

[TallSA]

Hi Nick404

Yes, that what I want to do.

Could you please provide me with query code.

Regards,

[TallSA]

Hi Nick404 and Ranman256,

Thank you for your help, it works fine now,

Thanks again.

Regards,