|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
July 15th, 2005, 01:58 PM
|
|||
|
|||
|
proper syntax for update query
I am trying to write an update query and I am having problems with
the firebird syntax. The query involves 2 tables that are identical in structure but one has updated data from another database I want to match up the keys then update the data from the one table to the second table An example that works in a Access but not in firebird is as follows Code:
UPDATE Contacts INNER JOIN Contacts1 ON Contacts.ContactID = Contacts1.ContactID SET Contacts.FirstName = Contacts1.FirstName, Contacts.LastName = Contacts1.LastName; What would be the proper syntax for Firebird. Thanks, Fr. Robert
|
|
#2
July 16th, 2005, 12:47 PM
|
|||
|
|||
|
this way you'll succeed:
Code:
UPDATE Contacts C
SET C.FirstName=(SELECT C1.FirstName FROM Contacts1 C1
WHERE C.ContactsID=C1.ContactsID),
C.LastName==(SELECT C1.LastName FROM Contacts1 C1
WHERE C.ContactsID=C1.ContactsID)
Lutz -- web based Firebird and InterBase administration: http://www.ibwebadmin.net
__________________
web based Firebird and InterBase administration: http://ibwebadmin.sf.net
|
|
#3
July 18th, 2005, 09:20 AM
|
|||
|
|||
|
I tried the above syntax for some reason I had to refer to the first table in the select statement. The statement generates another error
multiple rows in singleton select. It seems the syntax only works if you want to update one record. What syntax would you use if you had multiple records to update? Thanks, Fr. Robert
|
|
#4
July 18th, 2005, 01:30 PM
|
|||
|
|||
|
No, the syntax is working for multiple records. The 'multiple rows in singleton select' error means that the query fails because the subquery returns more than one value for a distinct record. It will work if you make sure that the subqueries return exact one value for every row.
Lutz -- web based Firebird and InterBase administration: http://www.ibwebadmin.net
|
|
| Viewing: Dev Shed Forums > Databases > Firebird SQL Development > proper syntax for update query |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
