1

I need to update all the rows on table A, where A.id = B.id and B.code is some value.

Something like:

UPDATE table_a
 SET processed = 'Y'
 WHERE table_a.id = table_b.id AND
       table_b.code = 'ABC';

Does anyone know the correct way to do this?

Thanks!

wallyk
  • 56,922
  • 16
  • 83
  • 148
James
  • 1,430
  • 4
  • 20
  • 27

2 Answers2

0

Here are quick workaround for your problem.

UPDATE 
(
 SELECT 
   a.processed, a.id 
 FROM 
   table_a a, 
   table_b b
 WHERE 
   a.id = b.id 
 AND 
   b.code = 'ABC'
 ) u
SET u.processed = 'Y'

OR

UPDATE table_a a
SET a.processed = 'Y'
WHERE a.id IN
(SELECT b.id FROM table_b b WHERE b.code = 'ABC')

Hope this might Help!

analyticalpicasso
  • 1,993
  • 8
  • 26
  • 45
-1

You have to reference second table before you can use it. You can use a subselect:

UPDATE table_a SET processed = 'Y' 
WHERE table_a.id = (SELECT table_b.id FROM table_b WHERE table_b.code = 'ABC');

The subselect returns a list of ids from table_b fulfilling the condition table_b.code = 'ABC'. Update will effect those rows in table_a with table_a.id in the list from subselect.

Tonia H
  • 99
  • 3