[RESOLVED] Update help

[sapator]

Hi.
I need some help with an update.
I have this:
Server1
CODE FORMAT
HO00105285 0000000002
HO00105285 VS00000001

Server2
CODE FORMAT
HO00105285 0000000002
HO00105285 VS00000001

I need to bypass those on an update and only do an update if the values have been changed on FORMAT

Doing this:

	UPDATE Server2 
	SET Format = HO.Format
	FROM Server1  as HO inner join  Server1  FF on FF.CODE COLLATE Greek_CI_AS = HO.CODE and FF.Format COLLATE Greek_CI_AS <> HO.Format

This will always update the above 2 lines even if they are equal on both servers on CODE's that are 2 liners or more.
How would I go about and ignore them and only update if the Format is different ?

[sapator]

Or at worse if not possible, to delete values that exist on Server1 (CODE + FORMAT) and not on Server1.
Something like this (does not work):

--delete from ---
	select CODE,Format  FROM Server1 FF WHERE  NOT EXISTS 
	(SELECT  HO.CODE,HO.Format
	 FROM Server2 HO Where FF.CODE  COLLATE Greek_CI_AS = HO.CODE AND FF.Format COLLATE Greek_CI_AS = FF.Format)

[Zvoni]

that exist on Server1 (CODE + FORMAT) and not on Server1.

??? Huh?

EDIT could you give us sample data for when you want to update and when you want to skip?
i do have an idea how to solve this, but your sample data doesn’t reflect your problem.
a DELETE/UPDATE on inequality is usually done with a LEFT JOIN and a IS NULL check in the WHERE-clause
but we would need to know what tocheck for (what is equal and what is not)

[sapator]

I guess I wanted to write exist on server 1 and not server 2.
What I can think of i as partition by , but I'll too exhausted now to think it over.

Instead of values I think this would help more
Server1
X , 1
X, 2

Server 2
X,1
X,1
OR
X,1
X,3

Who to turn Server 2 to
X,1
X,2

X,3 for example I think it needs to be deleted not updated because how about if I have
X,1
X,2
X,3
X,4
I won't have a clue what to update to X,2 .
But OK
Supposedly just
X,1
X,1 and need to change X,1 to X,2 again it's cherry picking.

[Zvoni]

This not going to work, at least not the way you want.
do you have any other field between the two tables, which are unique but should be the same in both?

[sapator]

I'm thinking to strict the field only on to one or delete and insert.
If I can pass by the general specs an one to one then it would be fine.

[Zvoni]

Then columns code and format should be together (!) unique, so there is only one possible combination of both.
then it would be easy

[sapator]

In any day scenario yes but due to multiple requirements I'm not sure if I need to "let it pass" and run a query search later on or make them unique. But that is something I need to discuss with another department.

[Zvoni]

I did some tests with the structure you provided, and i have to amend my statement:
It's not going to be easy, if you don't have a single unique per Row identifier which is the same in both tables.

For the structure as provided, the only thing coming to mind to just display your "Problem" would be a FULL OUTER JOIN.
Nevermind doing a DELETE or UPDATE on it

EDIT: Thinking about it a bit more.....
What i could gather from your description of the "problem":
1) You have a table "Server1" which is "clean", "correct" --> meaning it's kind of a "master"
2) You have a table "Server2", which for whatever reason gets entries from sources unknown,
which result in duplicate entries (your "x, 1" - "x, 1" above) and/or in entries that don't appear in "Server1" (your "x, 1" - "x, 3")
3) You want to clean up "Server2" and bring it to the same "state" as "Server1"

That correct?

[sapator]

Hey. After a meeting we are going to do it one liner so we don't get caught into this kind of trouble.
If you want for the philosophical heck of it to look it up then , yes what you wrote is correct , I'm updating any differences found (and here I had the X lines issues) and Inserting any new lines (that I don't have an issue there).

[Zvoni]

Hey. After a meeting we are going to do it one liner so we don't get caught into this kind of trouble.
If you want for the philosophical heck of it to look it up then , yes what you wrote is correct , I'm updating any differences found (and here I had the X lines issues) and Inserting any new lines (that I don't have an issue there).

OK, what i thought.
Right.
As follows:
1) De-duplicate "server2" --> Should be easy enough (combined unique or a SELECT with ROW_NUMBER Partitioned by Code and Format and then Deleting everything with a rownumber >1)
2) IIRC, you have MS SQL_Server: Use the MERGE-Statement with Server1 being Source, and Server2 being Target. That should Insert any missing records in S2, and delete any records in S2 which are not in S1
https://www.sqlservertutorial.net/sq...-server-merge/

Beware:
Since there is a DELETE involved, make sure there are no child-records in other tables depending on S2 (ON DELETE CASCADE!)

[sapator]

Thanks , turned out it was not THAT much double records so I did it by hand and also put a constrain (well 2Col PK to be exact) so only one record with one format can pass through.