Update query, how to get column name from other table

Question

I don't know if it's possible but this is what I want:

UPDATE `tbl_users` SET (SELECT `category` FROM `tbl_items` WHERE id = ?) = (SELECT `item` FROM `tbl_items` WHERE id = ?) WHERE id = ?

but this isn't possible I get this error in PHP:

Fatal error: Call to a member function bind_param() on a non-object in.

I want to update a column from tbl_users which name of the column is in tbl_items.

Just explain what you want using this query? – Sadikhasan Jun 28 '14 at 12:04 — Sadikhasan, Jun 28 '14 at 12:04
How `User` table and `Category` table related to each other – Sadikhasan Jun 28 '14 at 12:09 — Sadikhasan, Jun 28 '14 at 12:09

score 1 · Accepted Answer · answered Jun 28 '14 at 12:08

1

I'm not sure if I've understood your question, but could you do the first query then use the results of that query to do the second one? so:

"SELECT `category`, `item` FROM `tbl_items` WHERE `id`=?"

Then use the results for (assuming the row is held in $row):

"UPDATE `tbl_users` SET `".$row['category']."` = '".$row['item']."' WHERE `id`=?

answered Jun 28 '14 at 12:08

Luke

3,985
1
20
35

you did understand it :) but i already knew that, that will work but is it possible to do it in 1 statement? – Anwar Jun 28 '14 at 12:10
As far as I can tell, you can only use a subquery to set the value, not to idenfity the column. So `column = (SUBQUERY)` but not `(SUBQUERY) = (SUBQUERY)` – Luke Jun 28 '14 at 12:15
Thanks ! I will use your option – Anwar Jun 28 '14 at 12:16
No worries :) have you seen these? http://stackoverflow.com/questions/11588710/mysql-update-query-with-sub-query AND http://stackoverflow.com/questions/7216591/mysql-sub-query-select-statement-inside-update-query – Luke Jun 28 '14 at 12:20

score 0 · Answer 2 · answered Jun 28 '14 at 12:24

you can use this in one statment. you dont need two queries and fetch and so on.

    UPDATE `tbl_users` 
    INNER JOIN `tbl_items`  ON `tbl_users`.id = `tbl_items`.id
    SET `tbl_items`.`category`  = `tbl_items`.`item` 
    WHERE `tbl_users`.id = ?

Update query, how to get column name from other table

2 Answers2