How to write Inner Join Query in dqMan?

Question

We have two tables dbo.MONITOR (Reg. table) & sum_file_folder (documentum table)

dbo.MONITOR

- FILE_ID
- STATUS 

sum_file_folder

- object_name
- file_status 

where, FILE_ID & object_name are same fields in both tables.

So these Inner Join Queries are not working...

update dbo.MONITOR t1 objects set t1.STATUS = (select file_status from sum_file_folder t2 where t1.FILE_ID=t2.object_name)

UPDATE t1 OBJECTS SET t1.STATUS = t2.file_status from sum_file_folder t1 INNER JOIN dbo.MONITOR t2 ON t1.object_name = t2.FILE_ID

Answer 1

When you are using your own tables, you can safely do an update directly to database - no need for DQL (sum_file_folder must be a custom table - I haven't seen that before). If you are stuck with DQL you could do a EXECUTE exec_sql WITH QUERY 'your-sql-update-statement' Just be careful - this is auto-committed.

Answer 2

Looks like some syntax and table alias issue in your query.

Can you try the query below:

UPDATE t1 
SET STATUS = t2.file_status 
FROM dbo.MONITOR t1 
INNER JOIN dbo.sum_file_folder t2 ON t2.object_name = t1.FILE_ID

Answer 3

You can use this code to update the status in your table.

UPDATE t1
SET t1.STATUS = t2.file_status
FROM dbo.MONITOR t1 
INNER JOIN sum_file_folder t2 where t1.FILE_ID=t2.object_name