I know that I am not wise in the ways of SQL (just a newbie). However, how would this let me know which records failed both INSERT and UPDATE? I need to record them (the entirety of the data).
What am I missing?
-- $me = rand($hacker{perl});
Comment on Re^2: Superior way to update SQL tables
I assumed that updates would not fail. If updates can fail, too, then you're pretty much stuck going through row-by-row. You could at least determine which rows should be inserts and which should be updates, in advance, by checking whether a corresponding row exists in the real table:
$to_insert_stmt = q{
SELECT * FROM scratch S
WHERE session_id = ? AND NOT EXISTS (
SELECT 1 FROM real_data
WHERE key_val1 = S.key_val1 AND ...
)
};
$to_update_stmt = q{
SELECT * FROM scratch S
WHERE session_id = ? AND EXISTS (
SELECT 1 FROM real_data
WHERE key_val1 = S.key_val1 AND ...
)
};
Then any rows that fail insert are skips, and any rows that fail update are skips, and you don't waste time trying to insert rows that should be updates.
Caution: Contents may have been coded under pressure.