Re: Superior way to update SQL tables

This response has nothing to do with Perl, but you should be able to process your data with one insert and one update statement. One would be of the form

UPDATE real_table R
SET (col1, col2, col3,...)
  = SELECT col1, col2, col3,...
    FROM scratch S
    WHERE R.key_val1 = S.key_val1 AND R.key_val2 = S.key_val2 AND ...
;
[download]

and the other would be like

INSERT INTO real_table R
SELECT * FROM scratch S
WHERE NOT EXISTS (
  SELECT 1 FROM real_table
  WHERE key_val1 = S.key_val1 AND key_val2 = S.key_val2 AND ...
)
[download]

The snag with that sounds like some of the inserts might fail (since you mention skipping). At least you should be able to do the UPDATE, and delete those records. Then you can loop through what's left for INSERT/SKIP.

Caution: Contents may have been coded under pressure.

Comment on Re: Superior way to update SQL tables Select or Download Code

Replies are listed 'Best First'.
Re^2: Superior way to update SQL tables by radiantmatrix (Parson) on Sep 08, 2004 at 15:21 UTC
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});}	[reply]
Re^3: Superior way to update SQL tables by Roy Johnson (Monsignor) on Sep 08, 2004 at 16:16 UTC
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 ... ) };` [download] 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.	[reply] [d/l]