Changing the data after comparison

[prophecym]

Hi everyone,

 

I am sure there's a way to do this. I just don't know how. Any help is appreciated.

 

I have 2 tables that need to be compared based on their one particular field. Say this is the 'name' field. During the comparison of  these two tables, it needs to find the same records. Then, change the data in the another field to something else.

 

Table A                                        Table B

id | name | boolean                      name

 

As you can see above, the name field needs to be compared. When it finds the same records, the 'boolean' field in the Table A needs to be changed whatever I set to. (If it is 1, change it to 2)

 

I would love to know how to do this. Thank you.

[Christian F.]

What you need is an UPDATE statement, with a WHERE clause to limit the rows updated to what you want.

Something like this should work, though it might require you to join table B so try it on some test data first.

UPDATE `table_a` AS a SET a.`boolean` = $Val WHERE `table_b`.`name` = a.`name`

 

Another way of doing it is by using WHERE IN() and a subquery. A quick google search will give you lots of examples on how this is done.

[prophecym]

I have a mind blank. I couldn't get this to work. And I couldn't find If it is not too much trouble, would you write down what exactly should I write down?

[fenway]

You want to change a boolean field to "2"?

[prophecym]

You want to change a boolean field to "2"?

 

:confused: What was I thinking But I know that you know what I mean.

[Barand]

UPDATE tableA a
INNER JOIN tableB b ON a.name = b.name
SET a.boolean = 2
WHERE a.boolean = 1

[prophecym]

UPDATE tableA a
INNER JOIN tableB b ON a.name = b.name
SET a.boolean = 2
WHERE a.boolean = 1

 

Thank you very much, you just saved me from a ton of work.