How to tanslate to SQL?

[Thalassicus]

How can I translate the blue lua code to sql?


UPDATE Unit_ResourceQuantityRequirements
SET ResourceType = "RESOURCE_ALUMINUM"
WHERE ResourceType = "RESOURCE_OIL"
AND GameInfo.Units[UnitType].Domain = "DOMAIN_AIR";

 

[SamBC]

I don't think it can be done, at least not in SQLite. The best I can think of that might work would be to (temporarily) augment Unit_ResourceQuantityRequirements with the domain from the matching record, and I'm not sure that can be done just with SQL either.

That is, we know how to add a field, and how to remove it later, the question then is how to set the value of the new field to the value of a matching record in another field. In some SQL versions, it can be done with a subselect, like SET x = (SELECT single_field FROM other_table WHERE unique_field = y). Of course, a similar trick might work for the where clause.

 

[MouseyPounds]

You can probably do this with a subquery. For example the following works from the Firefox SQLite Manager console:

UPDATE Unit_ResourceQuantityRequirements
SET Cost=3 
WHERE ResourceType = "RESOURCE_OIL" 
AND UnitType IN (SELECT Type FROM Units WHERE Domain = "DOMAIN_AIR")

I changed the cost rather than the resource because it was easier for me to check that it did what I expected it to do. I would expect a similar statement to work in CivV too but didn't actually try it.

 

[SamBC]

You can probably do this with a subquery. For example the following works from the Firefox SQLite Manager console:

UPDATE Unit_ResourceQuantityRequirements
SET Cost=3 
WHERE ResourceType = "RESOURCE_OIL" 
AND UnitType IN (SELECT Type FROM Units WHERE Domain = "DOMAIN_AIR")

I changed the cost rather than the resource because it was easier for me to check that it did what I expected it to do. I would expect a similar statement to work in CivV too but didn't actually try it.

Ooh, I forgot about the IN operator. Dead handy.

 

[Thalassicus]

Thanks, I didn't know about the IN operator either!