[SQL] Updating one table with data from another
Hey all, There are two things I need to do: 1. Update existing rows with new data 2. Append new rows I need to update only some of the fields table1 with data from table2. These tables have the exact same fields. So here's what I have currently for appending new rows (rows where CID does not currently exist in table1, but have been added to table2): INSERT INTO table1 (field1, field2, ...) SELECT field1, field2, ... FROM table2 WHERE NOT EXISTS (SELECT CID FROM table1); But something is wrong with the logic there and I'm not quite getting it. For the update part, here's what I have: UPDATE table1 SET field1 = table2.field1 field2 = table2.field2, ..., FROM table1 INNER JOIN table2 ON table1.CID=table2.CID; I'm not sure what's wrong with this one either. Any help would be greatly appreciated! -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] query faster using LEFT OUTER join?
2.47 rows=721
width=18) (actual time=25.246..38.654 rows=770 loops=1)
-> Hash Left Join (cost=3706.71..4000.93 rows=721
width=8) (actual time=25.215..31.169 rows=770 loops=1)
Hash Cond: (i.loc_submission_id = ls.loc_submission_id)
-> Merge Left Join (cost=3420.14..3481.22 rows=721
width=12) (actual time=15.088..20.018 rows=770 loops=1)
Merge Cond: (i.loc_submission_id =
lsti.loc_submission_id)
-> Sort (cost=2576.82..2578.62 rows=720
width=8) (actual time=2.287..2.438 rows=768 loops=1)
Sort Key: i.loc_submission_id
-> Bitmap Heap Scan on instance i
(cost=25.24..2542.65 rows=720 width=8) (actual time=0.381..1.623
rows=768 loops=1)
Recheck Cond: (translation_pair_id
= ANY ('{640352,6144,1023028,18155,240244,50157}'::oid[]))
-> Bitmap Index Scan on
instance_translation_pair_id (cost=0.00..25.24 rows=720 width=0)
(actual time=0.293..0.293 rows=768 loops=1)
Index Cond:
(translation_pair_id = ANY
('{640352,6144,1023028,18155,240244,50157}'::oid[]))
-> Sort (cost=843.32..868.47 rows=10059
width=8) (actual time=12.782..14.312 rows=10530 loops=1)
Sort Key: lsti.loc_submission_id
-> Seq Scan on loc_submission_train_info
lsti (cost=0.00..174.59 rows=10059 width=8) (actual
time=0.012..3.708 rows=10059 loops=1)
-> Hash (cost=261.46..261.46 rows=10046 width=4)
(actual time=10.061..10.061 rows=10046 loops=1)
-> Seq Scan on loc_submission ls
(cost=0.00..261.46 rows=10046 width=4) (actual time=0.013..5.235
rows=10046 loops=1)
-> Index Scan using context_pkey on context c
(cost=0.00..6.81 rows=1 width=18) (actual time=0.008..0.009 rows=1
loops=770)
Index Cond: (i.context_id = c.context_id)
Thanks for your help,
Drew
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [SQL] How convert UNICODE
How about pg_dump the data from your old database, then load it into your new database which is using UTF8? Drew On Nov 17, 2006, at 9:09 AM, lms wrote: Hello I have 2 databases with same table. First database is in UNICODE, second in SQL_ASCII. 4 columns from first database I must convert to: (first column)iso8859-1, (second column)iso8859-2, (3-th column)iso8859-1, (4 column)iso8859-5. After it I must save these 4 rows in database in SQL_ASCII. How can I do it? It can be using libpq,libpgeasy, Thx. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] How to find missing values across multiple OUTER JOINs
ed Chinese | || | Spanish | || | Swedish | || | Traditional Chinese | || | Portuguese | || | Polish | || | Turkish | || | Czech | || | Brazilian Portuguese (20 rows) I'm guessing I need to group the joins together, to avoid some associative problem. Do you see what I'm doing wrong? Thanks for the help, Drew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How to find missing values across multiple OUTER JOINs
Thanks! That was it. Drew On Jun 6, 2007, at 1:45 AM, Ragnar wrote: On þri, 2007-06-05 at 23:55 -0700, Drew wrote: I'm having troubles using multiple OUTER JOINs, which I think I want to use to solve my problem. My problem is to find all non-translated string values in our translations database, given the 4 following tables: SOURCE (source_id PRIMARY KEY, language_id, value, ...) TRANSLATION (translation_id PRIMARY KEY, language_id, value, ...) TRANSLATION_PAIR (source_id, translation_id) LANGUAGE(language_id PRIMARY KEY, name) This seems to me the appropriate situation for using OUTER JOINs, but I cannot figure out how to get the null rows without the not-null rows. Here's my best guess at this query: SELECT s.source_id,tp.translation_pair_id,t.translation_id,t.language_id, l.name FROM source s LEFT OUTER JOIN translation_pair tp USING(source_id) LEFT OUTER JOIN translation t ON tp.translation_id = t.translation_id AND t.translation_id is null move this condition out of the ON clause into a WHERE clause ) RIGHT OUTER JOIN language l on l.language_id = t.language_id; SELECT s.source_id, tp.translation_pair_id, t.translation_id, t.language_id, l.name FROM source s LEFT OUTER JOIN translation_pair tp USING(source_id) LEFT OUTER JOIN translation t ON tp.translation_id = t.translation_id RIGHT OUTER JOIN language l on l.language_id =t.language_id WHERE t.translation_id is null; (i did not check the rest of your query) hope this helps, gnari ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] logging messages from inside pgPLSQL routine?
I'mm trying to debug something inside my PLSQL routine. How do I print out error messages from inside my function? Thanks, Drew ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
