1

I have a pretty simple MySQL query to implement, but I can't figure out how...

I have two tables, T1 and T2.

What I need to do:

From T1, I retrieve an ID based on a CODE value:

SELECT id FROM T1 WHERE code = '$code';

Then I need to use this ID (so the value I just retrieved) to update a specific row in T2 (the name of the row will match the ID's value).

I was thinking about using either subqueries or user-defined variables, but no matter how I try it I can't get it done.

If you have any code snippet that can help me doing that, I would appreciate it as well!

EDIT

Just to clarify something: I don't know the name of the column that I need to update in T2, since that name will be the value I retrieve from T1.

So for example, if the ID I get from T1 is "03", it will update the column named "03" in T2.

EDIT 2

Here's a little schema of what I intend to achieve (hoping I make myself clearer, I'm sorry for the misunderstanding...)

enter image description here

Community
  • 1
  • 1
Keysaw
  • 227
  • 4
  • 19
  • Possible duplicate of [MYSQL update with WHERE SELECT subquery error](https://stackoverflow.com/questions/6944165/mysql-update-with-where-select-subquery-error) – devlin carnate Feb 06 '18 at 17:03

5 Answers5

4
UPDATE T2 SET COL = YOUR_VALUE
WHERE T2.ID = (SELECT id FROM T1 WHERE code = '$code')

UPDATE: If the sub query returns more than one row then you can use from IN operator

UPDATE T2 SET COL = YOUR_VALUE
WHERE T2.ID IN (SELECT id FROM T1 WHERE code = '$code')
Elham Kohestani
  • 3,013
  • 3
  • 20
  • 29
4

Use an UPDATE with a JOIN:

UPDATE T2
CROSS JOIN T1
SET T2.`0` = IF(T1.id = 0, T1.someColumn, T2.`0`),
    T2.`1` = IF(T1.id = 1, T1.someColumn, T2.`1`),
    T2.`2` = IF(T1.id = 1, T1.someColumn, T2.`1`)
WHERE T1.code = '$code'

Replace someColumn with the column in T1 containing the value you want to put into T2.

Barmar
  • 741,623
  • 53
  • 500
  • 612
  • you also need to have `JOIN T1 ON T2.name = T1.id AND code = '$code'` or a `WHERE code = '$code'` here – Raymond Nijland Feb 06 '18 at 17:15
  • What are "name" and "column" in this example? Are they MySQL keywords or should they be part of my tables? – Keysaw Feb 06 '18 at 18:14
  • You wrote "the name of the row will match the ID's value". So `name` is the column that matches the ID's value. And `column` is the column you want to update in the rows that match – Barmar Feb 06 '18 at 19:25
  • Yes I think that wasn't very clear. The thing is I don't know the name of the column since that name will be defined by the ID that I get from the first table. I updated my question to explain it better. – Keysaw Feb 06 '18 at 23:33
  • Could you update the question to show sample data in the two tables and what the result of the update should be? – Barmar Feb 07 '18 at 05:56
  • This is becoming very unclear. Isn't `T1.id` the column being used to join the two tables? How can it also be used to determine which column should be updated? – Barmar Feb 07 '18 at 05:57
  • Ok I just edited my question, tell me if it's still unclear. :) – Keysaw Feb 07 '18 at 10:28
  • What is the final result supposed to look like? How do you determine which row in T2 to update? – Barmar Feb 07 '18 at 17:37
  • I've updated the answer with my latest guess at what you're trying to do. – Barmar Feb 07 '18 at 17:39
1

you can update without using the subquery just using join 

  update t2
  inner join t1 on t2.name = t1.id and t1.code ='$code'
  set t2.my_col = 'my_value'

but you should not use var in your query you are at risk for sql injection take a look at you mysql driver for param_binding

ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
0
UPDATE T2 SET COL = YOUR_VALUE WHERE EXISTS 
 (SELECT 1 FROM T1 WHERE T2.id=T2.id AND code = '$code')
cdaiga
  • 4,861
  • 3
  • 22
  • 42
0

You can update T2 with the next SQL if you expect at least one row from the inner query

UPDATE T2 SET COLUMN = VALUE 
WHERE T2.ID IN (SELECT ID FROM T1 WHERE CODE = $code)

Otherwise, if from T1 you are sure you will get only 1 record

UPDATE T2 SET COLUMN = VALUE 
WHERE T2.ID = (SELECT ID FROM T1 WHERE CODE = $code)
Giacky
  • 181
  • 10