| Author |
Topic |
|
Dufflepod
Starting Member
5 Posts |
 Posted - 2006-02-15 : 22:57:19
|
| Here's the culprit...UPDATE bookings AS b,rallies AS r SET b.charged=TRUE, r.charged=TRUE WHERE b.rally_id='123' AND r.rally_id='123'There will be multiple matches on rally_id in the bookings table and only one in the rallies table. This seems to be crying out for a JOIN but I can't figure it. Embedded C & assembler is my usual diet I'm afraid.Thanks (sheepishly) in advance,Dufflepod. |
|
|
shallu1_gupta
Constraint Violating Yak Guru
394 Posts |
Posted - 2006-02-15 : 23:05:29
|
| It seems from your query that you are trying to update fields of both the tables which is not allowed using a single update statement. post what exactly are you trying to achieve. |
 |
|
|
Dufflepod
Starting Member
5 Posts |
Posted - 2006-02-15 : 23:13:58
|
| The query as written does do both tables with the single UPDATE. If I check the fields values before/after on tables with a just a handfull of records, I can clearly see the UPDATE has worked. I just figured the query itself could be rewritten more 'cleanly'. |
|
|
|
shallu1_gupta
Constraint Violating Yak Guru
394 Posts |
Posted - 2006-02-15 : 23:58:48
|
| This is not SQL Server I guess.. |
|
|
|
Dufflepod
Starting Member
5 Posts |
Posted - 2006-02-16 : 00:00:55
|
| Sorry - I didn't think to say. It's MySQL. |
|
|
|
Dufflepod
Starting Member
5 Posts |
Posted - 2006-02-16 : 00:14:41
|
| In SQL Server would you have to do this as two seperate queries? |
|
|
|
shallu1_gupta
Constraint Violating Yak Guru
394 Posts |
Posted - 2006-02-16 : 00:27:49
|
| Yes..but i think you can modify ur query as ...UPDATE bookings AS b,rallies AS rSET b.charged=TRUE, r.charged=TRUEWHERE b.rally_id= r.rally_id AND r.rally_id='123' |
|
|
|
a_r_satish
Yak Posting Veteran
84 Posts |
Posted - 2006-02-16 : 00:37:18
|
| Multiple updates in different tables is not at all possible. May be something is wrong there. You can go thr the Update syntax and confirm that.may be you can do this..Update try1 set try1.b = 5where exists (select distinct try.b from try where try1.b = try.b)But there should be a relationship between tables.Regards,satish.r |
|
|
|
shallu1_gupta
Constraint Violating Yak Guru
394 Posts |
Posted - 2006-02-16 : 01:00:09
|
| I dont know if this is possible in MYSQL as per Dufflepod as i have never worker on MYSQL |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-02-16 : 01:13:53
|
quote:
Originally posted by Dufflepod
Sorry - I didn't think to say. It's MySQL.
Post your question at MySql forum of www.DBForums.comMadhivananFailing to plan is Planning to fail |
|
|
|
Dufflepod
Starting Member
5 Posts |
Posted - 2006-02-16 : 05:52:49
|
| This is all good info. Thanks for the help. |
|
|
|
|
Surely this query can be simplified?