Compare 2 Tables 1

[ashishsmith]

I have 2 tables february and march, both of them have 11 fields and there is no primary key (no unique key).

Data from both the tables have to be in 1 table. There is one condition to fulfill, if fixed 4 fields which has the same data in both the tables than overwrite the data from february with march data else add the records.

What I am trying to do is update February table with March data and than trying to check on the 4 fields, if they have same data than delete the field from february and continue adding the data from march table.

This is not working, whenever I compare or run a select query in the update query it asks for a parameter.

I don't know what to do. I hope some genius in sql helps me.

Thank you,

Ashish Smith

 

[JamesLean]

Try this:

--update exisitng feb data with march data
UPDATE feb
SET col5 = mar.col5,
  col6 = mar.col6,
  ...
  col11 = mar.col11
FROM feb JOIN mar ON feb.col1 = mar.col1
  AND feb.col2 = mar.col2
  AND feb.col3 = mar.col3
  AND feb.col4 = mar.col4

--insert new data from march
INSERT feb (col1, col2, ..., col11)
SELECT mar.col1, mar.col2, ..., mar.col11
FROM mar LEFT JOIN feb ON mar.col1 = feb.col1
  AND mar.col2 = feb.col2
  AND mar.col3 = feb.col3
  AND mar.col4 = feb.col4
WHERE feb.col1 IS NULL

--James

 

[earthandfire]

James I'm confused by the Join in the INSERT, surely all that is required is to determine that feb.col1 IS NULL?

 

[JamesLean]

You need to use the LEFT JOIN to see which rows in March do not appear in Feb, based on the unique 4-column key. You only need to insert the rows that do not already exist as the ones that do have already been updated with the previous UPDATE query.

--James

 

[earthandfire]

Thanks

 

[ashishsmith]

UPDATE query doesn't work with FROM, it can only have SET and WHERE. I tried your query for update it soesn't work.

What should I do?


Thank you,

Amar Shah

 

[JamesLean]

There's nothing wrong with my query as posted (UPDATE can have a FROM clause). Are you using SQL Server? If so, can you post your actual query?

--James

 

[ashishsmith]

I am using Ms Access Database but assume SQL Query Language is always same, whatever it is; here is my query:

UPDATE feb
SET ID=march.ID,
[Scan Group]=march.[Scan Group],
[Scan Date]=march.[Scan Date],
[Vuln ID]=march.[Vuln ID],
CVE=march.CVE,
Vulnerability=march.Vulnerability,
Summary=march.Summary,
Description=march.Description,
[Risk Level]=march.[Risk Level],
IP=march.IP,
Hostname=march.Hostname,
[O/S]=march.[O/S],
Recommendation=march.Recommendation,
[Original Date]=march.[Original Date],
Age=march.Age,
Status=march.Status,
[Target O/S]=march.[Target O/S],
[Target App]=march.[Target App]

FROM feb JOIN march ON feb.Vulnerabilities=march.Vulnerabilities
AND feb.[Risk Level] = march.[Risk Level]
AND feb.IP=march.IP
AND feb.[Original Date] = march.[Original Date];



When I try to save or execute this query, it says syntax error ('Missing Operator')

I hope this helps

Thank you,

Amar Shah

 

[Catadmin]

Actually, there are some differences between Access SQL and SQL Server T-SQL. I believe someone mentioned on another thread that the square brackets are invalid in SQL Server. (Someone check me on that?)

The problem is, since I make sure all my table names and column names don't have spaces, I honestly don't remember how to call a table/column with spaces. I think it's either double quotes or single quotes...



Catadmin - MCDBA, MCSA
"Just because I'm paranoid doesn't mean the universe *isn't* out to get me!"

 

[dbomrrsm]

Catadmin

square brackets are required in sql server where the column name etc has spaces in it.

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[Catadmin]

Ah, okay. I need to go find that thread and re-read it then.

Thanks, DBomrrsm.



Catadmin - MCDBA, MCSA
"Just because I'm paranoid doesn't mean the universe *isn't* out to get me!"

 

[Catadmin]

DOH! I'm stupid.

Your From statement is wrong.

UPDATE feb
SET ID=march.ID,
[Scan Group]=march.[Scan Group],
[Scan Date]=march.[Scan Date],
[Vuln ID]=march.[Vuln ID],
CVE=march.CVE,
Vulnerability=march.Vulnerability,
Summary=march.Summary,
Description=march.Description,
[Risk Level]=march.[Risk Level],
IP=march.IP,
Hostname=march.Hostname,
[O/S]=march.[O/S],
Recommendation=march.Recommendation,
[Original Date]=march.[Original Date],
Age=march.Age,
Status=march.Status,
[Target O/S]=march.[Target O/S],
[Target App]=march.[Target App]

From march where feb.Vulnerabilities=march.Vulnerabilities
AND feb.[Risk Level] = march.[Risk Level]
AND feb.IP=march.IP
AND feb.[Original Date] = march.[Original Date];

Catadmin - MCDBA, MCSA
"Just because I'm paranoid doesn't mean the universe *isn't* out to get me!"

 

[ashishsmith]

I copied the same code. It says

Syntax error(missing operator) in query expression 'march.[Target App] From march'.


If I try putting , after march.[Target App] than it says

syntax error in update statement.


I don't know whats wrong with this now.


Please help...

Ashish Smith

 

[dbomrrsm]

If you post this in the access forum i am sure you will get a quick response as to why it wont work.

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]