[mythtv-users] channel id change to mysql

[Simon Hobson]

Ismo Tanskanen wrote:

>I execute this sql query:
>
>UPDATE
>record,record_tmp,recordedcredits,recordedfile,recordedprogram,recordedseek,recordmatch,recordedmarkup,recorded,oldrecorded
>SET chanid=1004 WHERE chanid=1504
>
>And get:
>Error Code : 1052
>Column 'chanid' in field list is ambiguous
>(0 ms taken)
>
>But if I go table to table like this:
>UPDATE record SET chanid=1004 WHERE chanid=1504
>it works.
>
>Problem is that I have 23 channel id:s changed and there is 10 tables,
>it is 230 queries..
>What I'm doing wrong

I didn't know you could update multiple tables like that.

My guess is that because chanid is in more than one table, it doesn't 
know which one to use. But, and bear in mind I didn't know this was 
even legal syntax, I would have thought that you would have to key 
each table OR to join the tables. You see, if you simply put "where 
record.chanid=..." then every record of the other tables will match 
and so all records would get updated (I'm assuming this isn't what 
you want !).

So if it works at all (you DO have a backup don't you !), then I'd 
have thought you'd need to do something like :

where record.chanid=1504
   and record_tmp.chanid=record.chanid
   and recorded_credits.chanid=record.chanid
   and ...


-- 
Simon Hobson

Visit http://www.magpiesnestpublishing.co.uk/ for books by acclaimed
author Gladys Hobson. Novels - poetry - short stories - ideal as
Christmas stocking fillers. Some available as e-books.