CODEWITHSUNDEEP
sqlsqlite
Wasabi
Wasabi

Reputation: 3071

Update value if columns in different tables match

I have the following two tables:

CREATE TABLE CustomProperties (
      ID         INTEGER PRIMARY KEY
    , programID  INTEGER
    , Key        VARCHAR
)

CREATE TABLE XXXCustom (
      licID  INTEGER
    , propID INTEGER -- points to CustomProperties.ID
    , Value  VARCHAR
)

CustomProperties serves as a register of different types of custom properties which my programs can contain. XXXCustom stores the custom properties actually used by program XXX (another table stores properties shared by all programs).

Now, if I want to modify an object's custom property, I know both the Key and the Value I want to modify it to. However, I first need to "convert" the Key into the relevant ID, which I then use to locate the desired row in XXXCustom.

I tried

UPDATE XXXCustom
SET pp.Value = 'TEST'
FROM CustomProperties cp INNER JOIN XXXCustom pp
ON pp.propID = cp.ID AND pp.Key = 'test'

But this throws a syntax error, probably because UPDATE doesn't have a FROM clause, nor can you use JOIN directly in an UPDATE. Fair enough.

So I tried

UPDATE (SELECT cp.Key, pp.Value
        FROM CustomProperties cp INNER JOIN FtoolCustom pp
        WHERE cp.ID = pp.propID)
SET pp.Value = 'TEST'
ON pp.Key = 'test'

But this also throws a syntax error, I believe because UPDATE doesn't work on subqueries, but requires a qualified table name.

So now I'm out of ideas. Do I have to do this manually in steps: first "convert" the Key to its ID and then use that ID to update XXXCustom?

Upvotes: 2

Views: 54

Answers (1)

redneb
redneb

Reputation: 23870

How about:

UPDATE XXXCustom
SET value='myval'
WHERE licID=(SELECT id FROM CustomProperties WHERE key='mykey');

Upvotes: 2

Related Questions