Need a SQL statement focus on combination of tables but entries always with uinque ID
- by Registered User KC
Hi All,
I need SQL code to solve the tables combination problem, described on below:
Table old data:
name version status lastupdate ID
A 0.1 on 6/8/2010 1
B 0.1 on 6/8/2010 2
C 0.1 on 6/8/2010 3
D 0.1 on 6/8/2010 4
E 0.1 on 6/8/2010 5
F 0.1 on 6/8/2010 6
G 0.1 on 6/8/2010 7
Table new data:
name version status lastupdate ID
A 0.1 on 6/18/2010
#B entry deleted
C 0.3 on 6/18/2010 #version_updated
C1 0.1 on 6/18/2010
D 0.1 on 6/18/2010
E 0.1 off 6/18/2010 #status_updated
F 0.1 on 6/18/2010
G 0.1 on 6/18/2010
H 0.1 on 6/18/2010 #new_added
H1 0.1 on 6/18/2010 #new_added
the difference of new data and old date:
B entry deleted
C entry version updated
E entry status updated
C1/H/H1 entry new added
What I want is always keeping the ID - name mapping relationship in old data table no matter how data changed later, a.k.a the name always has a unique ID number bind with it.
If entry has update, then update the data, if entry is new added, insert to the table then give a new assigned unique ID.
However, I can only use SQL with simple select or update statement then it may too hard for me to write such code, then I hope someone with expertise can give direction, no details needed on the different of SQL variant, a standard sql code as sample is enough.
Thanks in advance!
Rgs
KC