0

I am working on an asset management system, and i am stuck. I want to update about 3 tables in one single query connected by some common id columns!

UPDATE assets 
JOIN asset_types ON assets.asset_type_id = asset_types.asset_type_id,  
JOIN assignment_station ON assets.assignment_station_id=assignment_station.assignment_station_id,
JOIN asset_statuses ON assets.asset_status_id = asset_statuses.asset_status_id
SET
assets.asset_type_id = 1,
assignment_station.assignment_station_name ='Foxdale Court',
assignment_station.assignment_station_manager ='John Dole',
assignment_station.assignment_station_manager_number = '09887765433',
asset_statuses.asset_status_id =3
WHERE assets.asset_id=1

enter image description here

But I cant seem to find any answers on stackoverflow or google.

Daniel Ngandu
  • 81
  • 1
  • 2
  • 11
  • 1
    Possible duplicate of [How to update two tables in one statement in SQL Server 2005?](https://stackoverflow.com/questions/2044467/how-to-update-two-tables-in-one-statement-in-sql-server-2005) – Masivuye Cokile Mar 19 '19 at 13:40
  • 2
    As currently written, this doesn't relate to PHP or mysqli. Does your query work and you are having issues implementing in PHP/mysqli? – user3783243 Mar 19 '19 at 13:43
  • 1
    MySQL should support that syntax. What issue are you having? – Gordon Linoff Mar 19 '19 at 14:02
  • The error is simply the entire query thrown back without any specific line or syntax error – Daniel Ngandu Mar 20 '19 at 14:43
  • @MasivuyeCokile i did check. Its not a duplicate. Kindly look at how many tables i want updated. – Daniel Ngandu Mar 20 '19 at 14:43
  • @user3783243 as my habit, i usually try in phpmyadmin/Heidisql if my queries work, before adding them to my php files. – Daniel Ngandu Mar 20 '19 at 14:44
  • 1
    So the query works in your UI but only fails in the PHP implementation? If so please add the PHP to the question, also show how you are handling the errors. – user3783243 Mar 20 '19 at 14:54
  • *the entire query thrown back without any specific line or syntax error* ... I have not seen but am very curious on such a raised error. After you post relevant PHP code block, please also post the entire error message or whatever shows in console, web page, or log file. – Parfait Mar 20 '19 at 15:40
  • 'UPDATE assets JOIN asset_types ON assets.asset_type_id = asset_types.asset_type_id, JOIN assignment_station ON assets.assignment_station_id=assignment_station.assignment_station_id, JOIN asset_statuses ON assets.asset_status_id = asset_statuses.asset_status_id SET assets.asset_type_id = 1, assignment_station.assignment_station_name ='Foxdale Court', assignment_station.assignment_station_manager ='John Dole', assignment_station.assignment_station_manager_number = '09887765433', asset_statuses.asset_status_id =3 WHERE assets.asset_id=1' – Daniel Ngandu Mar 21 '19 at 06:24
  • @Parfait, i run my queries in phpmyadmin before adding them to my php code – Daniel Ngandu Mar 21 '19 at 06:27
  • Here is a link to the error i got in phpmyadmin [link](https://wetransfer.com/downloads/aedb0f9fb9efdc811211634384c0630620190321062603/7a6ead105f594a633538473632190cf920190321062603/13790c) – Daniel Ngandu Mar 21 '19 at 06:27
  • @user3783243 the action of my form calls a php file that runs the update query, but like i mentioned, i test my queries in phpmyadmin before proceeding to add it to my php code. So if it doesnt work in phpmyadmin console, i dont add it to my php file. – Daniel Ngandu Mar 21 '19 at 06:30
  • 1
    So the query doesn't work in your DB UI either? `here is a link to the error i got in phpmyadmin` sounds like query fails in DB, before you should be putting it in PHP. I can't see the error. Please update the question with the error (as text, not an image). Also if that code you posted as a comment is different than the above code please add it to the question as well. – user3783243 Mar 21 '19 at 12:17
  • @GordonLinoff kindly see my prevous comments on this thread. I have resorted to use separate update statements to update my tables for the time being, but i would like to find out if i could do that with one query to update more than 3 tables. – Daniel Ngandu Mar 21 '19 at 13:59

0 Answers0