Using EXISTS operator with UPDATE statement

[ITcore]

I'm trying to edit some values for something added by DLC but I don't want to make it crash the game if someone doesn't have that DLC. I know about using SELECT/FROM/WHERE for INSERT INTO statements but I'm not sure what to use for UPDATE. I *think* it's the following:

UPDATE table
SET column = 'value'
WHERE condition = 'value'
AND EXISTS (SELECT * FROM DLCtable WHERE DLCcondition = 'DLCvalue');

Is that the correct use?

 

[LeeS]

You can also use "1" in place of the * symbol, like as this for a civ5 mod I made

UPDATE Technologies 
SET GridX = GridX + 2
WHERE Type = 'TECH_LRSDUMMY_POSTMODERN'
AND EXISTS (SELECT 1 FROM Eras WHERE Type = 'ERA_ENLIGHTENMENT');

 

[ITcore]

And which tag should I use in my modinfo? Reference, right?

 

[isau]

I like LeeS' method slightly better as a programmer because its clearer to me what is happening.

FWIW this is how I do it for my MyOptions system in my Combined Tweaks mod. There is a file called MyOptions where players can change a handful of UPDATE statements that write to a table called tblQuoOptions (which is populated as the first step in the setup files of the mod). Then anywhere in my mod I want to have a setting change something I code it basically like this:

-- Change Pikemen into Ranged units if that option is enabled in MyOptions
UPDATE Units SET Range=1, RangedCombat = Combat -7, Combat = Combat +7 
    WHERE  PromotionClass = 'PROMOTION_CLASS_ANTI_CAVALRY'
    AND EXISTS (SELECT tblQuoOptions.Value FROM tblQuoOptions WHERE tblQuoOptions.OptionID='QUO_OPTION_ARE_ANTI_CAV_RANGED' AND tblQuoOptions.Value >= 1);

 

[ITcore]

I like LeeS' method slightly better as a programmer because its clearer to me what is happening.

FWIW this is how I do it for my MyOptions system in my Combined Tweaks mod. There is a file called MyOptions where players can change a handful of UPDATE statements that write to a table called tblQuoOptions (which is populated as the first step in the setup files of the mod). Then anywhere in my mod I want to have a setting change something I code it basically like this:

I'm a network administrator, I work with command prompts, not code lol. If using a 1 or just typing the table name will work, I'll do it. I just saw someone use * and that's what I put up there. I appreciate the help! I've gotten way better with SQL than when I first started but LUA looks like the Matrix still. Baby steps.

 

[LeeS]

And which tag should I use in my modinfo? Reference, right?

Unfortunately References in the modinfo don't actually accomplish much of anything in Civ6.

Dependancies work but since the Summer17 patch they only require the other mod to be enabled, and do not create a load order for files to be loaded into the game's database.

You're actually probably better off in your case giving a <Properties> <LoadOrder> setting to your <Update Database> actions with a value such as "10" for the LoadOrder setting. This is usually sufficient to ensure all the files within your <UpdateDatabase> action load after all the dlc have loaded their content into the game's database.

 

[LeeS]

I like LeeS' method slightly better as a programmer because its clearer to me what is happening.

William Howard taught me that one. Using * appeared to give him hives. Sadly, William is no longer modding.

 

[ITcore]

Thanks for the help, LeeS and isau.

 

[ITcore]

Unfortunately References in the modinfo don't actually accomplish much of anything in Civ6.

Dependancies work but since the Summer17 patch they only require the other mod to be enabled, and do not create a load order for files to be loaded into the game's database.

You're actually probably better off in your case giving a <Properties> <LoadOrder> setting to your <Update Database> actions with a value such as "10" for the LoadOrder setting. This is usually sufficient to ensure all the files within your <UpdateDatabase> action load after all the dlc have loaded their content into the game's database.

Oh, one last thing.

Can the WHERE condition be a column in the DLC before I use the EXISTS operator? Example:

UPDATE Improvements
SET Housing = 2
WHERE ImprovementType = 'IMPROVEMENT_OUTBACK'
AND EXISTS (SELECT 1 FROM Improvements WHERE ImprovementType = 'IMPROVEMENT_OUTBACK');

I'm assuming this is okay because the AND operator is there to force both conditions be met. Am I on the right track?

 

[isau]

Oh, one last thing.

Can the WHERE condition be a column in the DLC before I use the EXISTS operator? Example:

UPDATE Improvements
SET Housing = 2
WHERE ImprovementType = 'IMPROVEMENT_OUTBACK'
AND EXISTS (SELECT 1 FROM Improvements WHERE ImprovementType = 'IMPROVEMENT_OUTBACK');

I'm assuming this is okay because the AND operator is there to force both conditions be met. Am I on the right track?

If you are just trying to update the Outback Station's values you can just do this without the second SELECT:

UPDATE Improvements
SET Housing = 2
WHERE ImprovementType = 'IMPROVEMENT_OUTBACK' ;


That's because if that ImprovementType doesn't exist nothing happens at all. (NOTE: Also FYI in the database for Australia the improvement is named IMPROVEMENT_OUTBACK_STATION so I'm not sure if you're referring to that or something else.)


Here's a recent example where I updated the Khmer unique unit:

UPDATE Units SET BaseMoves=BaseMoves+1 WHERE UnitType='UNIT_KHMER_DOMREY' ;

If Khmer aren't installed nothing happens at all.


Now, where it CAN become an issue is if you have something like this:

UPDATE UnitUpgrades SET UpgradeUnit='UNIT_KHMER_DOMREY' WHERE Unit='UNIT_CATAPULT' ;

That's because UNIT_KHMER_DOMREY in that case is a foreign key to the Units table. So for that you would need to check first that UNIT_KHMER_DOMREY exists.

 

[ITcore]

Now, where it CAN become an issue is if you have something like this:

UPDATE UnitUpgrades SET UpgradeUnit='UNIT_KHMER_DOMREY' WHERE Unit='UNIT_CATAPULT' ;

That's because UNIT_KHMER_DOMREY in that case is a foreign key to the Units table. So for that you would need to check first that UNIT_KHMER_DOMREY exists.

So in that case, it would be

UPDATE UnitUpgrades
SET UpgradeUnit='UNIT_KHMER_DOMREY'
WHERE Unit='UNIT_CATAPULT'
AND EXISTS (SELECT 1 FROM Units WHERE UnitType = 'UNIT_KHMER_DOMREY');

What I'm trying to do is include altered values of some new resources added by a mod. When I tried using normal code, it failed to load because it couldn't find the resources I was referencing. But I was inserting values into tables other than Resources so ResourceType wasn't technically created yet because of some issues with load order. I'm trying to include the code so I don't force people to get the mod if they don't want it.

I hope I understood that right. Foreign key is a column reference in a different table than where it was originally created?

 

[isau]

I hope I understood that right. Foreign key is a column reference in a different table than where it was originally created?

Yep.

In literal terms you can tell if something is a foreign key by looking at its implementation in the game files. (Personally I use SQLite Studio, you can view this easily by right clicking and selecting Edit Table and looking at its DDL).


Here's a look at the Projects table for example:

In the table definition above, when the PrereqTech column is created it is defined as a Foreign Key reference to the Technologies table (TechnologyType) field. Basically that means its value is constrained to values that already exist in Technologies.

 

[ITcore]

That makes so much sense. Thanks!

So I only need the EXISTS operator when the UPDATE is changing a foreign key? Primary keys can be referenced without EXISTS and it won't crash the rest of the code if the user doesn't have the necessary DLC/mod?

 

[isau]

That makes so much sense. Thanks!

So I only need the EXISTS operator when the UPDATE is changing a foreign key? Primary keys can be referenced without EXISTS and it won't crash the rest of the code if the user doesn't have the necessary DLC/mod?

Mostly yes.

If you haven't already done so, I highly recommended downloading a SQL editor like SQLite Studio. It is useful for writing queries against the database and testing to see if they will cause issues ahead of time versus having to load up the game and check.