Geek Answers Handbook

The SQL statement needs orientation on a combination of tables, but records are always with a unique identifier

I need SQL code to solve the table combination problem described below:

Old data table: old table

    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

New data table: new table

    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                #new_added
    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

difference of new data and old date:

B record deleted

Updated login version C

Record Status Updated

C1 / H / H1 new added

What I want always maintains a name identifier matching relationship in the old data table no matter how the data subsequently changes, aka name is always associated with it by a unique identification number.

, , , , . , .

, SQL select update, , , -, , , , SQL, sql- .

!

Rgs

======== sql , , , - pls comment, !

1.duplicate old table as tmp

tmp *

2.update tmp, ""

tmp ( )

3. "" (old vs new) tmp

tmp ( ) set idvar = max ( max (id) tmp) + 1 ( new.name new.version new.status new.lastupdate new.ID , old.name < > new.name)

4. tmp (, B)

tmp (???)

+5
7

, , SQL Server, - SQL MERGE. .: http://www.mssqltips.com/tip.asp?tip=1704

MERGE , . "", "" . , . MERGE , , , , .

:

MERGE Products AS TARGET
USING UpdatedProducts AS SOURCE 
ON (TARGET.ProductID = SOURCE.ProductID) 
--When records are matched, update 
--the records if there is any change
WHEN MATCHED AND TARGET.ProductName <> SOURCE.ProductName 
OR TARGET.Rate <> SOURCE.Rate THEN 
UPDATE SET TARGET.ProductName = SOURCE.ProductName, 
TARGET.Rate = SOURCE.Rate 
--When no records are matched, insert
--the incoming records from source
--table to target table
WHEN NOT MATCHED BY TARGET THEN 
INSERT (ProductID, ProductName, Rate) 
VALUES (SOURCE.ProductID, SOURCE.ProductName, SOURCE.Rate)
--When there is a row that exists in target table and
--same record does not exist in source table
--then delete this record from target table
WHEN NOT MATCHED BY SOURCE THEN 
DELETE
--$action specifies a column of type nvarchar(10) 
--in the OUTPUT clause that returns one of three 
--values for each row: 'INSERT', 'UPDATE', or 'DELETE', 
--according to the action that was performed on that row
OUTPUT $action, 
DELETED.ProductID AS TargetProductID, 
DELETED.ProductName AS TargetProductName, 
DELETED.Rate AS TargetRate, 
INSERTED.ProductID AS SourceProductID, 
INSERTED.ProductName AS SourceProductName, 
INSERTED.Rate AS SourceRate; 
SELECT @@ROWCOUNT;
GO
+1

:

# 4 tmp; , WHERE tmp.name NOT IN (SELECT name FROM new); # 3 - , .

№2, auto increment ID?

№1, tmp- , , # 2- # 4 , (, , ) new .

(!), new ID ( ID) , (!).

, , , .

, (php/mysql).

, , SET, INSERT, DELETE SELECT ( ).

+1

. , :-)

- - . -

table_original

name     version    status    lastupdate
A        0.1        on        6/8/2010
B        0.1        on        6/8/2010
C        0.1        on        6/8/2010
D        0.1        on        6/8/2010
E        0.1        on        6/8/2010
F        0.1        on        6/8/2010
G        0.1        on        6/8/2010

name_id

name     ID 
A        1 
B        2 
C        3 
D        4 
E        5 
F        6 
G        7

table_new

  • TRUNCATE table_original
  • INSERT INTO name_id ( table_new name_id)
  • table_new table_original

. , .

, .

A , , a. , A b. ?

b. Deleted? name_id

4. set Deleted? = Y, table_original

2. Deleted? = Y.

name_id , , table_old, - . , , table_new,

+1

Informix , . MySQL, . , , .

SELECT DISTINCT name FROM old
UNION
SELECT DISTINCT name FROM new
INTO TEMP _tmp;

SELECT 
  CASE WHEN b.name IS NULL THEN ''
       ELSE aa.name
       END AS name, 
  CASE WHEN b.version IS NULL THEN ''
       WHEN a.version = b.version THEN a.version 
       ELSE b.version
       END AS version,
  CASE WHEN a.status = b.status THEN a.status 
       WHEN b.status IS NULL THEN ''
       ELSE b.status
       END AS status,
  CASE WHEN a.lastupdate = b.lastupdate THEN a.lastupdate 
       WHEN b.lastupdate IS NULL THEN null
       ELSE b.lastupdate
       END AS lastupdate,
  CASE WHEN a.name IS NULL THEN '#new_added'
       WHEN b.name IS NULL THEN '#' || aa.name || ' entry deleted'
       WHEN a.version  b.version THEN '#version_updated'
       WHEN a.status  b.status THEN '#status_updated'
       ELSE ''
  END AS change
  FROM _tmp aa
  LEFT JOIN old a
         ON a.name = aa.name
  LEFT JOIN new b
         ON b.name = aa.name;
+1

, , ...

CREATE TRIGGER auto_next_id            UPDATE table SET uid = max (uid) + 1;   END;

0

, , , , , , table old, , , , ?

: new - ( ) : - : new - ( ) : new - ( )

, , , : (a) old new, (b) new, (c) .

(a) UPDATE new SET ID = IFNULL ((SELECT ID FROM old WHERE new.name = old.name), 0);

(b) UPDATE new SET ID = FUNCTION_TO GENERATE_ID (new.name) WHERE ID = 0;

(c) ;   CREATE TABLE old ( * );

, SQL , (b) sql . SQL Server, newid(), postgresql ( ), () , ( , , MySQL, , -, )

: , , sqlite python. str (uuid.uuid4()) (uuid module) python uuid ID , ID = 0 (b). , , , .

0

Why aren't you using a UUID for this? Create it once for the plug-in and include / enable it in the plugin, not in the database. Now that you mention python, here's how to create one:

import uuid
UID = str(uuid.uuid4()) # this will yield new UUID string

Of course, this does not guarantee global uniqueness, but the likelihood that you will get the same line in your project is rather low.

0
source

More articles:


All Articles