SQL update table2 if table1 has value X?

Question

IF EXISTS (SELECT * FROM table1 WHERE group_id='8')
UPDATE table2 SET (...) 
WHERE usertype='numbereight'

what am i doing wrong? I need to update the usertype based on the group_id

Mahmoud Gamal · Answer 1 · 2012-06-28T14:37:25.670

1

Try this instead:

UPDATE table2 
SET someColumn = 'somevalue'
WHERE EXISTS(SELECT * FROM table1 WHERE group_id='8')
AND usertype='numbereight'

edited Jun 28 '12 at 14:37

answered Jun 28 '12 at 14:31

Mahmoud Gamal

score 1 · Answer 2 · answered Jun 28 '12 at 14:36

1

You can try with something like this :

UPDATE table2
SET (...) 
WHERE usertype='number eight' AND EXIST(SELECT * FROM table1 WHERE group_id='8');

answered Jun 28 '12 at 14:36

aleroot

score 1 · Answer 3 · answered Jun 28 '12 at 14:37

1

Try

UPDATE table2 
SET (...) 
WHERE usertype = 'numbereight'
AND exists (select * from table1 where group_id = '8')

answered Jun 28 '12 at 14:37

Steven Mastandrea

score 1 · Answer 4 · answered Jun 28 '12 at 14:49

1

You can join the tables and update whatever fields you need

UPDATE t1 JOIN t2 ON t1.id = t2.id
SET
  t2.field = 'whatever'
WHERE
  t1.group_id= '8' AND
  t2.usertype = 'numbereight'

answered Jun 28 '12 at 14:49

Jeff

This is assuming that there is an ID in common to join on. If you look at the original question/SQL, there is no related ID/data so this would not work. – Steven Mastandrea Jun 28 '12 at 16:17
@StevenMastandrea You're correct. I assumed the tables were related somehow – Jeff Jun 28 '12 at 16:39

4 Answers4