Updating data using a SELECT query (with two related tables)

Question

I have been trying to update the data behind a SELECT query, but not getting too far, so I have created a new database, with the hope of finding a solution.

I have two tables:

table_a and table_b

table_a:
ID, item_to_match, new_value *(this is initially blank)*

table_b:
IDB, item_to_match, new_value *(the value I want to get into table_a)*

Somehow, I want to update new_value in table_a with new_value from table_b - but I can't seem to get it (I am new to this).

I tried creating an UPDATE query, but that only allowed me to show one of the two tables. So I tried creating a master query (called main), thinking I could create an UPDATE query on that - but I can't get that to work either. Here is the syntax:

SELECT
    `table_a`.`new_value` AS `goingto`,
    `table_b`.`new_value` AS `takenfrom`
FROM (`table_a`
    JOIN `table_b`
        ON ((`table_a`.`item_to_match` = `table_b`.`item_to_match`)))

I really, really would appreciate a bit more guidance on how to do this (sorry to keep asking).

I have now tried three variations and keep getting the same error message. But here are the variations:

UPDATE table_a, table_b
SET table_a.new_value = table_b.new_value
WHERE table_a.item_to_match = table_b.item_to_match

UPDATE table_a a
INNER JOIN table_b b ON a.item_to_match = b.item_to_match
SET a.new_value = b.new_value

UPDATE table_a a,table_b b
SET a.new_value = b.new_value
WHERE a.item_to_match = b.item_to_match

The field item_to_match is not a primary key or indexed (FYI)...

What the heck am I doing wrong???

I also just tried:

 UPDATE table_a a
    JOIN table_b b
    ON a.item_to_match = b.item_to_match
    SET a.new_value = b.new_value

Is the syntax right....am I doing something wrong in the app itself?

Muhammad Raheel · Accepted Answer · 2014-05-24T14:04:18.790

1

Here is how you can do it

UPDATE table_a AS a
INNER JOIN table_b AS b ON a.item_to_match = b.item_to_match
SET a.new_value = b.new_value

edited May 24 '14 at 14:04

answered May 24 '14 at 07:15

Muhammad Raheel

19,823
7
67
103

Thank Raheel - but I keep running into the same error, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE table_a a "new line" INNER_JOIN table_b b ON a.item_to_match = b.item_to_match "new line" S' at line 5 – AndyDB May 24 '14 at 13:18

score 0 · Answer 2 · edited May 23 '17 at 12:05

0

You don't need to say which table(s) you're updating, that's implicit in your SET clause.

UPDATE tableA a
JOIN tableB b
   ON a.a_id = b.a_id
JOIN tableC c
   ON b.b_id = c.b_id
SET b.val = a.val+c.val
WHERE a.val > 10
    AND c.val > 10;

There is no FROM clause.
Please refer this question asked in SO.

edited May 23 '17 at 12:05

Community

1
1

answered May 24 '14 at 13:59

unknown

4,859
10
44
62

I tried a slightly altered version which (as well as yours), but got the following message when attempting to save it.- Object reference not set to an instance of an object. – AndyDB May 24 '14 at 14:08
check out this answer asked in SO : http://stackoverflow.com/questions/8331687/mysql-update-a-joined-table/8331868#8331868 – unknown May 24 '14 at 14:13
I think it's me.....I have just written the following query "UPDATE table_a SET new_value = 'fred'" and it gives me the same error when I try to save it (as a View). I am using DevArt Fusion within .NET. – AndyDB May 24 '14 at 14:19
So it might be your query specific problem, not a syntax problem. – unknown May 24 '14 at 14:20
1

It could be (but at least I'm getting a really good overview of UPDATE queries.... – AndyDB May 24 '14 at 14:23

Updating data using a SELECT query (with two related tables)

2 Answers2