MrD MrD - 3 months ago 10
MySQL Question

Selecting a value from one of three tables

I have four tables: map, project, test, instance.
An instance is associated to a test, and a test is associated to a project.
The three tables: project, test, and instance, all have a map_id column (which can be null).

I want to update an 'instance' entry with a map_id. This map_id should first come from test's map_id; if that is null, then use the project's map_id.

I can do this with code and more than one SQL query; is there a way to do this in a single update query?

If not, I will probably do this in a new entry trigger, but I would prefer to do this in a single update query after-the-fact.

Schema:

map
+map_id

project
+project_id
+map_id

test
+test_id
+project_id
+map_id

instance
+instance_id
+test_id
+map_id

Answer

You can use join and ifnull

update instance
inner join test on instance.test_id = test.test_id
inner join project on test.project_id = project.project_id
set instance.map_id = ifnull(test.map_id,  project.map_id);

eventually add where for filtering

update instance
inner join test on instance.test_id = test.test_id
inner join project on test.project_id = project.project_id
set instance.map_id = ifnull(test.map_id,  project.map_id)
where instance.instance_id =  your_value;