SQL (@!#$... bloody) Update queries

[Draakie]

Hiya all !

Hopefully a brainiac can help me out.

I have a "setup type table" in a database that has a boolean value - that needs to initialize another table(s)
if the criteria is'nt already existing, deleting records that are earmarked as not used:

Example :

(Table 1)
Currency_Swift    |  UseSwift
-------------------------------------------------------------
USD                       |     X                           <-- this Currency swift code will be used
AUS                       |                                   <- this Currency swift code will NOT be used
ZAR                        |     X                            <- this Currency swift code will be used
EUR                       |     X                            <- this Currency swift code will be used         

(Table 2)
Branch_no          |  Swift_Code
-------------------------------------------------------------
3                           |   AUS                         <- this record would need to be removed seeing as "AUS" is not beieng used in Table 1.
3                           |   ZAR                          <- for this branch the "ZAR" record needs to stay unmodified.

.......a new record must be created for branch "3" to add the "USD" Currency Swift code as that has been marked for use in Table 1.
.......a new record must be created for branch "3" to add the "EUR" Currency Swift code as that has been marked for use in Table 1.

4                           |  EUR                          <- for this branch the "EUR" record needs to stay unmodified.

.......a new record must be created for branch "4" to add the "USD" Currency Swift code as that has been marked for use in Table 1.
.......a new record must be created for branch "4" to add the "ZAR" Currency Swift code as that has been marked for use in Table 1.

I'am looking for the appropriate SQL - statements to accomplish this.

PLEASE HELP - my brain and patience is falling apart.

Draakie

[FORTRANS]

Hi,

   Unfortunately the best I can come up with is to add all when
building the second table and then go back and delete the
duplicates and unwanted ones.  Ugly, but simple minded.

Good luck,

Steve N.

[donkey]

Might try this, not sure if the syntax is valid since I have no tables handy I can try it on and I'm not sure SQLite supports it anyway. However you will definitely need an INNER JOIN.

Code Select Expand

SELECT * FROM Table2 INNER JOIN Table1 ON Table2.Swift_Code=Table1.Currency_Swift WHERE Table1.UseSwift='X'

[Glenn9999]

an inner join returns all items that are in both tables in common with the join things.  A left join returns all items in the left table and the right table items, if they exist.

But that said, you actually want to use the EXISTS keyword.

http://www.techonthenet.com/sql/exists.php

Again it's hard to give you something tested since we don't have your tables in front of us, but the examples in that link should help.

[donkey]

Hi Glen9999,

You're absolutely right, I misread his her post completely and thought he she was just looking to return a list of valid rows.

edit: Whoops, I stand corrected, my apologies