[FIXED] How I can use Substring for Update in SQL right?

Issue

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%added' GROUP BY File

Now I want to update the table data. The column 'user' and the column 'Timestamp' are to be transferred from the table history to data. My last attempt for the update command was the following:

update data d
set 
d.user = v.user,
d.upload= h.`timestamp` 
inner join
history h on d.contract = h.contract 
where 
d.filename in (SELECT DISTINCT SUBSTRING(h.action,LOCATE('"',h.action)+1,(((LENGTH(h.action))-LOCATE('"', REVERSE(h.action))-1)-LOCATE('"',h.action)))) and h.action like 'File%added'

Either I received a syntax error so far or it was a SQL command which returned 0 rows to modify. I hope that is an easy problem to fix.

Thank you in advance for your help!

Solution

I don't know if below query would help, because as I mentioned in the comment you should provide data examples and expected results.

The correct UPDATE syntax is:

Single-table syntax:

UPDATE [LOW_PRIORITY] [IGNORE] table_reference [PARTITION (partition_list)] [FOR PORTION OF period FROM expr1 TO expr2] SET col1={expr1|DEFAULT} [,col2={expr2|DEFAULT}] ... [WHERE where_condition] [ORDER BY ...] [LIMIT row_count] Multiple-table syntax:

UPDATE [LOW_PRIORITY] [IGNORE] table_references SET col1={expr1|DEFAULT} [, col2={expr2|DEFAULT}] ... [WHERE where_condition]

So your update query should be:

update data d
inner join history h on d.contract = h.contract 
set  d.user = v.user, d.upload= h.`timestamp` 
where d.filename in (SELECT DISTINCT SUBSTRING(h.action,LOCATE('"',h.action)+1,(((LENGTH(h.action))-LOCATE('"', REVERSE(h.action))-1)-LOCATE('"',h.action)))) 
and h.action like 'File%added' ;

Answered By - Ergest Basha

Answer Checked By - Senaida (PHPFixing Volunteer)