Wasabi Wasabi - 2 months ago 5
SQL Question

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
?

Answer

How about:

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