-1

I'm trying to set the value of a column 'VALUE' where the 'ID' equals 2 AND the 'USERID' matches another row in the same table, where the 'ID' equals 1 and the VALUE is, say, 'London' in the example...

So:

ID | USERID | VALUE

1  | 1      | London
1  | 2      | Madrid
1  | 3      | London
1  | 4      | Paris
2  | 1      | null
2  | 2      | null
2  | 3      | null
2  | 4      | null

Becomes

ID | USERID | VALUE

1  | 1      | London
1  | 2      | Madrid
1  | 3      | London
1  | 4      | Paris
2  | 1      | on
2  | 2      | null
2  | 3      | on
2  | 4      | null

Is this possible?

If anyone can help I would be most grateful! Thanks.

lukekeast
  • 1
  • 2
  • Use an `UPDATE` query with a `JOIN`. – Barmar Apr 14 '20 at 20:04
  • See https://stackoverflow.com/questions/12394506/mysql-update-table-based-on-another-tables-value – Barmar Apr 14 '20 at 20:04
  • Thank-you so much @Barmar Apologies for making a rookie error. I promise I have tried looking for some time, but I was struggling to find the correct place to start - your links have helped tremendously to get me in the right direction, thank you! – lukekeast Apr 14 '20 at 20:09

1 Answers1

1

In MySQL, you can use a join in an update:

update t join
       t t2
       on t2.userid = t.userid and t2.value = 'London'
    set value = 'on'
    where t.id = 1
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Inicidentally, does using `AS` to declare the alias detract from something/anything other than readability for a beginner? – Andrew Morton Apr 14 '20 at 20:13
  • 1
    @AndrewMorton . . . This doesn't use `as`. I use it for column aliases - there is a small advantage if you use it well because it can help spot missing commas. I don't use it for table aliases because I find no use for it. But there is no harm in using it. – Gordon Linoff Apr 15 '20 at 00:27