once again I have a problem in SQL. I want to use a substring, which I otherwise used for a SELECT query, for a table UPDATE. The query looks like this:
SELECT SUBSTRING(h.action,LOCATE('"',h.action) 1,(((LENGTH(h.action))-LOCATE('"', REVERSE(h.action))-1)-LOCATE('"',h.action))) AS File,
h.TIMESTAMP,
h.user,
d.uid,
d.size
d.id
from history h
INNER JOIN data d ON h.contract = d.contract
LEFT JOIN history ON d.user = history.user
WHERE ( SELECT DISTINCT SUBSTRING(h.action,LOCATE('"',h.action) 1,(((LENGTH(h.action))-LOCATE('"', REVERSE(h.action))-1)-LOCATE('"',h.action)))) IN (SELECT filename FROM data)
and h.contract=xy AND h.action LIKE 'file