Update query with sub-query

Todd Cary

Delphi Developer

Sat, 07 Feb 2004 01:57:51 GMT

Update query with sub-query

I have two tables:

AcctType & DialBank

I want to UPDATE AcctType the field, BankName, with the value of the
field, BankName, in the DialBank table WHERE AcctType.BankID =
DialBank.BankID.

This syntax does not work. Any help would be greatly appreciated......

UPDATE AcctType SET AcctType.BankName = DialBank.BankName WHERE
DialBank.BankName IN (SELECT DialBank.BankName WHERE AcctType.BankID =
DialBank.BankID);

Todd

--
Todd Cary
Ariste Software
t...@aristesoftware.com

Todd Car

Delphi Developer

Sat, 07 Feb 2004 02:38:10 GMT

Re:Update query with sub-query

Figured out the solution:

Cmd := 'UPDATE AcctType SET BankName=B.BankName FROM AcctType A ' +
'INNER JOIN DialBank B ON A.BankID=B.BankID';

--
Todd Cary
Ariste Software
t...@aristesoftware.com

John Edward Scot

Delphi Developer

Sat, 07 Feb 2004 05:20:43 GMT

Re:Update query with sub-query

Quote

"Todd Cary" <t...@aristesoftware.com> wrote in message

news:3B814F9F.ED6CD847@aristesoftware.com...

Quote

> I have two tables:

> AcctType & DialBank

> I want to UPDATE AcctType the field, BankName, with the value of the
> field, BankName, in the DialBank table WHERE AcctType.BankID =
> DialBank.BankID.

> This syntax does not work. Any help would be greatly appreciated......

> UPDATE AcctType SET AcctType.BankName = DialBank.BankName WHERE
> DialBank.BankName IN (SELECT DialBank.BankName WHERE AcctType.BankID =
> DialBank.BankID);

> Todd

> --
> Todd Cary
> Ariste Software
> t...@aristesoftware.com

You don't appear to have a FROM clause in your subquery.

John.

Other Threads

1. How to do an Update query or a Delete Query or an Insert Query using D3

2. Doing an update using an aggregate sub-query

3. Would you ALL please let me know what went wrong with the Update Query and SubQuery?

4. UPDATE QUERY WORKS BUT CANT SEE CHANGES IN OTHER QUERY

5. Query from a Query, into a Query

6. Sub Query is Sooooooo Sloooooow (D2/W95/Paradox7)

7. Sub-queries in Delphi Database

8. SQL problem - joins plus sub query (Paradox7)

9. Speeding up a sub-query

10. Sub-Queries with TQuery - How to ?