How to make comparison between two tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to make comparison between two tables

Author

Topic

shonre89
Starting Member

5 Posts

Posted - 2014-06-20 : 03:59:06

This is my scenario.
i have two tables
1)FM_SHARE_RO_MOVED (5 COLUMNS)
2)PARSE_FILE INSTANCE (16 COLUMN)

column in FM_SHARE_RO_MOVED
File_name
FG00E3PK.csv

column in PARSE_FILE INSTANCE
File_Location
D:\ToParse\FLEXSTAR\PNG\FLEXSTAR12\FG00E3PK.csv

I want to check if the values in file name in FM_SHARE_RO_MOVED is available in PARSE_FILE_INSTANCE. And if it is available the update a column in FM_SHARE_RO_MOVED
Status
OK

Thanks in advance

-SHON-

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-06-20 : 04:46:15

[code]
UPDATE f
SET Status = 'OK'
FROM FM_SHARE_RO_MOVED f
WHERE EXISTS
(
SELECT *
FROM PARSE_FILE INSTANCE p
WHERE p.File_Location LIKE '%' + f.File_name + '%'
)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

shonre89
Starting Member

5 Posts

Posted - 2014-07-01 : 03:21:27

quote:

Originally posted by khtan


UPDATE f
SET    Status = 'OK'
FROM   FM_SHARE_RO_MOVED f
WHERE  EXISTS
       (
           SELECT *
           FROM   PARSE_FILE INSTANCE p
           WHERE  p.File_Location LIKE '%' + f.File_name + '%'
       )

KH
[spoiler]Time is always against us[/spoiler]

TQ very much
it works with a few changes. Just removed the p


UPDATE f
SET    Status = 'OK'
FROM   FM_SHARE_RO_MOVED f
WHERE  EXISTS
       (
           SELECT *
           FROM   PARSE_FILE INSTANCE 
           WHERE  File_Location LIKE '%' + f.File_name + '%'
       )

-SHON-

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-07-01 : 10:27:18

The alias 'p' is not really required in this case. It is just a habit of mine to assign an alias to all tables.

There is also no harm of having it there

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-07-01 : 11:34:07

Also see http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=194981

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2014-07-02 : 13:35:13

quote:
Originally posted by khtan

The alias 'p' is not really required in this case. It is just a habit of mine to assign an alias to all tables.

There is also no harm of having it there

KH
[spoiler]Time is always against us[/spoiler]

But there could be harm with not having it there. Sub-queries have access to the outer queries columns - and that could lead to ambiguous results or incorrect results.

If, for example - the column File_Location exists in the outer query but does not exist in the inner (sub) query. This could evaluate to a true statement causing invalid results.

By including the alias - and referencing the columns using the alias you are assured that the columns you are using do in fact reside in the correct table and the results will be what is expected.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources