CODEWITHSUNDEEP
mysql
No Sssweat
No Sssweat

Reputation: 418

How to join a specific table row?

table_one

+------+-------------+
| id   | cool        |
+------+-------------+
| 1    | 1.58        |
| 2    | 8.88        |
+------+-------------+

table_two

+------+-------------+
| id   | okies       |
+------+-------------+
| 1    | 2.15        |
| 2    | 7.50        |
+------+-------------+

table_result

+------+-------------------+
| id   | result            |
+------+-------------------+
| 1    | 1.58 (min value)  |
| 2    | 7.50 (min value)  |
+------+-------------------+

Howdy, I am somewhat familiar with mysql. I am using the least() function to find the minimum value between table_one and table_two:

INSERT INTO table_result (id, result)
select table_one.id, least(table_one.cool, table_two.okies) val
from table_one 
  join table_two on table_one.id = table_two.id

ON DUPLICATE KEY UPDATE id=VALUES(id), result=VALUES(result)

This code works perfectly, except I do not want to (evaluate) get the minimum value of all the table rows (id 1 and id 2). I want to evaluate/target a specific row id (ex: id 2 only!).

I tried adding WHERE id = 2 but I kept getting errors.

How can I achieve this?

Upvotes: 1

Views: 62

Answers (1)

No Sssweat
No Sssweat

Reputation: 418

What errors are you getting? Perhaps you just need to alias the id column in your where critera... – sgeddes

The error I was getting was Column 'id' in where clause is ambiguous. Turns out after researching this error, I needed to specify which table id, as both tables have id.

INSERT INTO table_result (id, result)
select table_one.id, least(table_one.cool, table_two.okies) val
from table_one 
  join table_two on table_one.id = table_two.id
  WHERE table_one.id = 2
ON DUPLICATE KEY UPDATE id=VALUES(id), result=VALUES(result)

Upvotes: 2

Related Questions