Updating a table

1,095 registered users | 0 active users | 0 LpH | Statistics
Login | Create New User

Welcome to IWETHEY!

Post #233,978 by pwhysall 11/14/05 6:24:45 AM 11/14/05 6:27:05 AM Reply	Updating a table Scenario: I have tables H and E. In terms of their definitions, they are identical. Table H contains a subset of records in E, but also some records that are not in E. I want to add the new records in table H to table E. My SQL-fu is weak, and the only tool I have for this job is Access. Clue me? Peter [link\|http://www.no2id.net/\|Don't Let The Terrorists Win] [link\|http://www.kuro5hin.org\|There is no K5 Cabal] [link\|http://guildenstern.dyndns.org\|Home] Use P2P for legitimate purposes! Edited by pwhysall Nov. 14, 2005, 06:27:05 AM EST Expand All History
Post #234,006 by ChrisR 11/14/05 10:33:45 AM Reply	Some possibilities `INSERT INTO foo\nSELECT \nFROM bar b\nWHERE (b.keyid NOT IN(SELECT f.keyid FROM foo f))` Or,... `INSERT INTO foo\nSELECT \nFROM bar b\nWHERE NOT EXISTS(SELECT f.keyid FROM foo f WHERE f.keyid = b.keyid)` or,.... `INSERT INTO foo\nSELECT *\nFROM \n bar b\n LEFT OUTER JOIN foo f ON(f.keyid = b.keyid)\nWHERE f.keyid IS NULL`
Post #234,007 by pwhysall 11/14/05 10:34:31 AM Reply	Much thanks I'll have a go with those. Cheers. Peter [link\|http://www.no2id.net/\|Don't Let The Terrorists Win] [link\|http://www.kuro5hin.org\|There is no K5 Cabal] [link\|http://guildenstern.dyndns.org\|Home] Use P2P for legitimate purposes!
Post #234,164 by static 11/14/05 9:45:08 PM Reply	I've learnt so much SQL since I last played with Access... I'd try something like. INSERT INTO e SELECT h.* FROM h LEFT JOIN e USING (key) WHERE e.key IS NULL ... which is pretty much Chris's third suggestion. Wade. "Insert crowbar. Apply force."
Post #236,681 by tablizer 11/30/05 7:24:59 PM Reply	Learning SQL via Access like lrning human rghts from Sadam ________________ oop.ismad.com
Post #234,220 by jbrabeck 11/15/05 9:50:32 AM Reply	Why not just use an Access append query Append H to E. Dups will be dropped. Or am I missing something here? A good friend will come and bail you out of jail ... but, a true friend will be sitting next to you saying, "Damn...that was fun!"

Updating a table - (pwhysall) - (5) - Nov. 14, 2005, 06:27:05 AM EST

Some possibilities - (ChrisR) - (1) - Nov. 14, 2005, 10:33:45 AM EST

Much thanks - (pwhysall) - Nov. 14, 2005, 10:34:31 AM EST

I've learnt so much SQL since I last played with Access... - (static) - (1) - Nov. 14, 2005, 09:45:08 PM EST

Learning SQL via Access like lrning human rghts from Sadam -NT - (tablizer) - Nov. 30, 2005, 07:24:59 PM EST

Why not just use an Access append query - (jbrabeck) - Nov. 15, 2005, 09:50:32 AM EST

iwethey.org

Ignore the man talking to his hand.
47 ms