CODEWITHSUNDEEP
mysqlsql
MrD
MrD

Reputation: 639

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

Upvotes: 1

Views: 42

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133370

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;

Upvotes: 1

waflija
waflija

Reputation: 177

You could use if-else staments inside your SQL, for example http://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html (MsSQL and Oracle may differ slightly) On the other hand this could make your code really hard to debug.

Upvotes: 1

Related Questions