NoPublic, bureaucrat, administrator
6,632
edits
Changes
no edit summary
[[Image:Check attachment1.png|700px|center]]
<li> Now the field ''guid'' table ''test'' should be populated. The value is part of the file name and therefore the UPDATE query has to be run for each case.
:'''Example query'''
<pre>
UPDATE test
SET
guid = substring(f1 from (position('0a' in f1) ) for 37)
WHERE
(((( length(f1) - length(replace(f1, '.', ''))) = 4)))
AND
left(substring(f1 from (position('0a' in f1) ) for 37),2) = '0a' AND
substring(f1 from (position('0a' in f1) ) for 37) not like '%.%'
AND guid is NULL
</pre>
<li>Then compare the tables ''attachment'' and ''test''
:'''Found in both''' (in my example 2034)
<pre>
SELECT
"public".attachment.filename,
"public".test.f1
FROM
"public".attachment
INNER JOIN "public".test ON "public".attachment.attachment_guid = "public".test.guid
</pre>
:'''Missing in the folder''' (in my example 2731)
<pre>
SELECT
"public".attachment.filename,
"public".test.f1
FROM
"public".attachment
LEFT JOIN "public".test ON "public".attachment.attachment_guid = "public".test.guid
WHERE
"public".test.guid IS NULL
</pre>
:'''Missing in the table''' (in my example 6625)
<pre>
6625
</pre>
</ol>
[[Category:NoPublic]]
{{NavBox Hub}}