SQL Update using other query results

Question

I have looked at SQL update query using joins ..but it is not exactly what I need (I am used to cook-book SQL, and can't figure out how to modify the answers).

I have a table "a" and a table "b". I want to update field f1 in "a" if and only if "b" has a record with fields f1, f2, f3 equal to f1, f2, f3 in table "a".

So, if I have

TABLE a
f1            f2            f3         ...
------------------------------------------
m             a1a 1a1       1-1-1980
m             a1a 1a1       1-1-1980
m             b1b 1b1       18-1-1982
m             c1c 1c1       16-4-1975

TABLE b
f1            f2            f3         ...
------------------------------------------
m             b1b 1b1       18-1-1982
m             c1c 1c1       16-4-1975

Do update. Then Table "a" looks like:

TABLE a
f1            f2            f3         ...
------------------------------------------
m             a1a 1a1       1-1-1980
m             a1a 1a1       1-1-1980
m             b1b 1b1       *-1-1982
m             c1c 1c1       *-4-1975

I know how to do the string manipulation... but the update query is not something I'm used to, especially in this context. I would appreciate your help.

Thank you!

EDIT: I am using sqlite

EDIT 2: Here is a query that user suggested, but it is not working currently to update records (probably my mistake):

UPDATE diseases
SET dateofbirth='*'||ltrim(dateofbirth,0123456789)
WHERE gender=(SELECT a1.gender FROM diseases AS a1 
              INNER JOIN stage2helper AS b ON     a1.gender = b.gender AND 
a1.dateofbirth = b.dateofbirth AND a1.postalcode = b.postalcode) 

AND postalcode=(SELECT a1.postalcode FROM diseases AS a1 INNER JOIN stage2helper AS b   ON a1.gender = b.gender AND a1.dateofbirth = b.dateofbirth AND a1.postalcode = b.postalcode) 

AND dateofbirth=(SELECT a1.dateofbirth FROM diseases AS a1 INNER JOIN stage2helper AS     b ON a1.gender = b.gender AND a1.dateofbirth = b.dateofbirth AND a1.postalcode =     b.postalcode);

Dale M · Accepted Answer · 2012-12-03T07:25:57.460

0

In Transact-SQL you can do this

UPDATE a
SET    a.f3=yourfunctiontogeneratetext()
FROM   b
WHERE  a.f1=b.f1 AND a.f2=b.f2 AND a.f3=b.f3

Ok with ANSI SQL you can't use the from syntax so try

UPDATE a
SET    a.f3=yourfunctiontogeneratetext()
WHERE  a.f1 = (SELECT a1.f1
               FROM a as a1
                    iNNER JOIN
                    b ON  a.f1=b.f1 AND a.f2=b.f2 AND a.f3=b.f3) and
       a.f2 = (SELECT a1.f2
               FROM a as a1
                    iNNER JOIN
                    b ON  a.f1=b.f1 AND a.f2=b.f2 AND a.f3=b.f3) and
       a.f3 = (SELECT a1.f3
               FROM a as a1
                    iNNER JOIN
                    b ON  a.f1=b.f1 AND a.f2=b.f2 AND a.f3=b.f3)

I did this on a tablet so please forgive any typos

edited Dec 03 '12 at 07:25

answered Dec 03 '12 at 03:21

Dale M

2,453
1
13
21

Thanks for your reply! I am using sqlite, which doesn't appear to support 'FROM' http://www.sqlite.org/lang_update.html I will try this though – Michael Dec 03 '12 at 03:24
this doesn't appear to work: I get Error: near "a": syntax error from sqlite – Michael Dec 03 '12 at 04:46
This is my actual query: UPDATE diseases a SET a.dateofbirth='*'||ltrim(a.dateofbirth,0123456789) FROM stage2helper b WHERE a.gender = b.gender AND a.postalcode = b.postalcode AND a.dateofbirth = b.dateofbirth – Michael Dec 03 '12 at 04:50
I am still looking for a way to solve this problem without using 'FROM', since sqlite doesn't support that. It does not necessarily have to be a super-efficient simulation of user1757836's solution; O(n^2) would be acceptable. – Michael Dec 03 '12 at 07:17
The from syntax is soooo much easier – Dale M Dec 03 '12 at 07:33
Ok; so there are no more syntax errors, and the subqueries that were defined return single columns, as you'd expect.. However, I am not sure how a.f1 = (...) will make this work; the interpreter accepts the whole query as valid SQL, but it does not actually update any records. – Michael Dec 03 '12 at 07:52
For example: if a and b have all the same exact records, doing a select * from a where a.f1=(..) and a.f2=(..) and a.f3=(..) should return every record, but it currently only returns one, for some reason – Michael Dec 03 '12 at 08:03
I should say that it actually updates only one record: the very first one in the table... the rest are left alone – Michael Dec 03 '12 at 08:17
If you wrap a select statement around the where clause, what do you get? – Dale M Dec 03 '12 at 10:31
Error: near "SELECT": syntax error is what I get when I wrap SELECT() around the WHERE – Michael Dec 03 '12 at 16:25

SQL Update using other query results

1 Answers1