StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>If I understood well what you need based on the comments in the two tables, I think you can simplify a lot your problem if you don't merge or update the old table because what you need is table new with the IDs in table old when they exist and new IDs when they do not exist, right?</p> <p>New records: table new has the new records already - OK (but they need a new ID) Deleted Records: they are not in table new - OK Updated Records: already updated in table new - OK (need to copy ID from table old) Unmodified records: already in table new - OK (need to copy ID from table old)</p> <p>So the only thing you need to do is to: (a) copy the IDs from table old to table new when they exist (b) create new IDs in table new when they do not exist in table old (c) copy table new to table old.</p> <p>(a) UPDATE new SET ID = IFNULL((SELECT ID FROM old WHERE new.name = old.name),0);</p> <p>(b) UPDATE new SET ID = FUNCTION_TO GENERATE_ID(new.name) WHERE ID = 0;</p> <p>(c) Drop table old; CREATE TABLE old (select * from new);</p> <p>As I don't know which SQL database you are using, in (b) you can use an sql function to generate the unique id depending on the database. With SQL Server, newid(), With postgresql (not too old versions), now() seems a good choice as its precision looks sufficient (but not in other databases as MySQL for example as I think the precision is limited to seconds)</p> <p>Edit: Sorry, I hadn't seen you're using sqlite and python. In this case you can use str(uuid.uuid4()) function (uuid module) in python to generate the uuid and fill the ID in new table where ID = 0 in step (b). This way you'll be able to join 2 independent databases if needed without conflicts on the IDs.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload