MYSQL选择更新查询多个连接错误

i am trying to update a variable row, in a variable table. This is my query:

我试图在变量表中更新变量行。这是我的查询:

// EDIT: removed double...

//编辑:删除了双...

UPDATE `ec`.`category_id` AS `category_id`,`e`.`title` AS `title`,
    `r`.`first_name` AS `first_name`,
    `r`.`last_name` AS `last_name`,`r`.`email` AS `email`,
    `r`.`comment` AS `comment`,`r`.`amount` AS `amount`,
    `r`.`published` AS `published`,`r`.`transaction_id` AS `transaction_id`,
    `r`.`register_date` AS `register_date`,
max((case `f`.`id` when 1 then `v`.`field_value` else '' end)) AS `pand`,
max((case `f`.`id` when 52 then `v`.`field_value` else '' end)) AS `achternaam`,
max((case `f`.`id` when 53 then `v`.`field_value` else '' end)) AS `voornaam`,
max((case `f`.`id` when 20 then `v`.`field_value` else '' end)) AS `gebdat`,
max((case `f`.`id` when 32 then `v`.`field_value` else '' end)) AS `geslacht`,
max((case `f`.`id` when 31 then `v`.`field_value` else '' end)) AS `kleinkind`,
max((case `f`.`id` when 21 then `v`.`field_value` else '' end)) AS `straat`,
max((case `f`.`id` when 54 then `v`.`field_value` else '' end)) AS `postcode`,
max((case `f`.`id` when 55 then `v`.`field_value` else '' end)) AS `plaats`,
max((case `f`.`id` when 26 then `v`.`field_value` else '' end)) AS `telthuis`,
max((case `f`.`id` when 27 then `v`.`field_value` else '' end)) AS `telmir`,
max((case `f`.`id` when 28 then `v`.`field_value` else '' end)) AS `gsmdeelnemer`,
max((case `f`.`id` when 29 then `v`.`field_value` else '' end)) AS `gsmpapa`,
max((case `f`.`id` when 56 then `v`.`field_value` else '' end)) AS `gsmmama`,
max((case `f`.`id` when 30 then `v`.`field_value` else '' end)) AS `graad`,
max((case `f`.`id` when 88 then `v`.`field_value` else '' end)) AS `bestelling`,
max((case `f`.`id` when 34 then `v`.`field_value` else '' end)) AS `eigendom`,
max((case `f`.`id` when 57 then `v`.`field_value` else '' end)) AS `zodiac`,
max((case `f`.`id` when 42 then `v`.`field_value` else '' end)) AS `tshirt`,
max((case `f`.`id` when 39 then `v`.`field_value` else '' end)) AS `helpdag`,
max((case `f`.`id` when 40 then `v`.`field_value` else '' end)) AS `helpinfo`,
max((case `f`.`id` when 36 then `v`.`field_value` else '' end)) AS `vervoerjanee`,
max((case `f`.`id` when 37 then `v`.`field_value` else '' end)) AS `vervoerinfo`
from ((((`dat_eb_field_values` `v` join `dat_eb_registrants` `r` on((`v`.`registrant_id` = `r`.`id`)))
join `dat_eb_fields` `f` on((`v`.`field_id` = `f`.`id`)))
join `dat_eb_events` `e` on((`r`.`event_id` = `e`.`id`)))
join `dat_eb_event_categories` `ec` on((`ec`.`event_id` = `e`.`id`)))
where ((`ec`.`category_id` = 4) and (`e`.`published` = 1))
SET $rowname=$newvalue WHERE transaction_id=$transid

Normally this query uses SELECT as the first argument instead of UPDATE. The last line was also added by me. $rowname, $newvalue and $transid are all defined and it geives me the following error:

通常,此查询使用SELECT作为第一个参数而不是UPDATE。最后一行也是我添加的。 $ rowname,$ newvalue和$ transid都已定义,它给我带来以下错误:

Not unique table/alias: 'first_name'.

Thanks in advance, Laurent

提前谢谢,劳伦特

2 个解决方案

#1

The syntax for UPDATE clause is something like this

UPDATE子句的语法是这样的

UPDATE table JOIN table1 ON table.field = table1.field SET table.field2 = 'value' WHERE table.field3 = 'value2'

You don't provide any fields to select as UPDATE clause does not select anything. So, you should remove the fields from SELECT clause and add the WHERE pats after SET.

您不提供任何要选择的字段,因为UPDATE子句不会选择任何内容。因此,您应该从SELECT子句中删除字段,并在SET之后添加WHERE pats。

UPDATE 
((((`dat_eb_field_values` `v` join `dat_eb_registrants` `r` on((`v`.`registrant_id` = `r`.`id`)))
join `dat_eb_fields` `f` on((`v`.`field_id` = `f`.`id`)))
join `dat_eb_events` `e` on((`r`.`event_id` = `e`.`id`)))
join `dat_eb_event_categories` `ec` on((`ec`.`event_id` = `e`.`id`)))

SET $rowname=$newvalue

WHERE ((`ec`.`category_id` = 4) and (`e`.`published` = 1)) AND transaction_id=$transid

#2

Problem is here

问题在这里

`r`.`first_name`,`r`.`first_name` AS `first_name`,

You have ambiguous aliases.

你有不明确的别名。

#1