Update value based on a value in another table

Question

Tried various different options for this and just running into a road block.

I have two tables. Every time stringvalue is 'Minor' in customefieldvalue table I want to update priority to 4 in the issue table.

UPDATE issue SET priority = '4'
FROM customfieldvalue
WHERE customfieldvalue.stringvalue = 'Minor';

Answer 1

I assume the tables are related in some way, let's say on the IssueId field. So you could do something like this:

UPDATE issue
SET priority = '4' 
WHERE IssueId IN (SELECT IssueId FROM customfieldvalue WHERE stringvalue = 'Minor')

You probably also want a condition in the where clause in the nested query to narrow down the custom field to be just the one you are after rather than any custom field with the value 'Minor', like:

WHERE customfieldname = 'PRIORITY' AND stringvalue = 'Minor'

and if you are going to be running this all the time, then you should filter out the ones that already have the correct value in the main where clause:

WHERE priority <> '4' AND IssueId IN (...