2 Tables A & B :: Search Value in B, Find Match & Update in A

[OldWest]

I am very new to writing SQL queries direct into phpMyAdmin.

 

Honestly. I am not sure where I should start from where I am now with SQL to properly run my query and update the records as I need to, so any advice is much appreciated on this.

 

This is what I've got:

 

Table A.

 

Table B.

 

Table A has 2 (1,2) Columns.

 

Table B has 4 (1,2,3,4) Columns.

 

Each column in both tables has 5 digits. There are 3 sets of 5 digits: Table A 1,2 - Table B 1,2 & 3,4. Each of these 3 groups should act as a full ten digits. So if Table A: Field 1 - 14325 :: Field 2 - 78467 - In my query, the result would be 1432578467.

 

I need to write an query that:

 

1) Scans Table B Columns 1 & 2.

 

2) Finds matches in Table A Columns 1 & 2.

 

3) Replaces any matched values with Table B Columns 3 & 4.

 

I was told this can be easily done in a few minutes with a LEFT JOIN, but that does not make much sense to me.

 

[mikosiko]

I was told this can be easily done in a few minutes with a LEFT JOIN

 

few minutes is too much time for that

 

but that does not make much sense to me.

 

why not?.... what have you tried so far?

[OldWest]

Hi mikosiko,

 

I lost a job offer because I could not solve this problem in time. I started writing a php app to do this for me, but that was taking too long.

 

I've tried various VB scripts in excel and other find and replace methods to no avail.

 

I've got about 11 hours in this and at this point, for the sake of learning, I am just trying to get off my feet on how to do this with a SQL query.

 

I don't know what a LEFT JOIN would have to do with finding and replacing records, so that does not make much sense at all. Of course the table would need to be joined, but as far as searching, finding and updating as required - I don't think a JOIN can do all that out of the box!

[OldWest]

This is what I have got so far. I decided to combine all fields into one table for simplicity.

 

SELECT codes WHERE prod_code_1="*" AND prod_code_2="*"; 
LIKE old_code_1 AND old_tax_code_1;
REPLACE prod_code_1 AND WITH  prod_code_ new_code_1 AND new_code_1;

 

This is not working as it stands, but I will describe my logic for critique:

 

Line 1: Selecting all prod codes.

Line 2: Prod codes matching old codes.

Line 3: Replacing matching old codes with new codes.

 

Am I at all on the right track here? SOS.

 

[OldWest]

Here is another variation I am working out. Does not work. Don't tell me what to do!, just what I might be doing wrong please:

 

UPDATE codes
WHERE (old_code_1.old_code_2 = prod_code_1.prod_code_2)
SET prod_code_1.prod_code_2
WHERE (old_code_1.old_code_2 = new_code_1.new_code_2)

 

[mikosiko]

an answer for your question in your first post (using 2 tables and a JOIN):

 

UPDATE tableA AS a
  JOIN tableB AS b ON concat(a.field1, a.field2) = concat(b.field1, b.field2)
SET a.field1 = b.field3,
    a.field2 = b.field4

// Replace table names and fields names accordingly

 

the 2 new intents that you posted doesn't make to much sense... , but with a little effort and considering that now you have all in one table your intent 2 is easy to fix and make it work...

 

[OldWest]

mikosiko,

 

Thank you for easing my mind... If I understand your note correct, my second approach - using the same table, has some ground to actually work??

 

And am I right in my understanding that the "." in prod_code_1.prod_code_ is a concatenation operator in SQL?

 

UPDATE codes
WHERE (old_code_1.old_code_2 = prod_code_1.prod_code_2)
SET prod_code_1.prod_code_2
WHERE (old_code_1.old_code_2 = new_code_1.new_code_2)

 

[OldWest]

Alright.. Well I am running the query that was provided for a great peace of mind now and it works great 

 

BUT now I am trying to add 00000 (5 zeros)

WHERE a.prod_code_1 != b.old_code_1

and trying to

SET b.prod_code_1 = '00000'

....

 

Obviously with my luck and research it's not working! But it seems like it should be according to the references I've been reading..

 

SO basically the original query is perfectly fine, but I want to add 00000 to any prod_codes_1's that do not have a match to old_prod_code_1..

 

What am I doing wrong?

[OldWest]

 

So completed code looks like this what I have now:

 

UPDATE product_codes_1b AS a
  JOIN old_and_new_codes_1a AS b ON concat(a.prod_code_1, a.prod_code_2) = concat(b.old_code_1, b.old_code_2)
SET a.prod_code_1 = b.new_code_1,
    a.prod_code_2 = b.new_code_2,
WHERE 	
a.prod_code_1 != b.old_code_1,
SET
b.prod_code_1 = '00000'

[OldWest]

mikosiko,

 

Well. I am trying to process all of these records in one table for simplicity reasons, and based on a working example, I removed what I felt was not needed - which of course is not working!

 

UPDATE codes_update_no_join_1a
concat(orig_code_1, orig_code_2) = concat(old_code_1, old_code_2)
SET orig_code_1 = new_code_1,
       orig_code_2 = new_code_2

 

[OldWest]

Well I've managed to keep the SQL error near the SET (hopefully the rest of the SQL is OK).. But this is what I've come to , and of course is still not working properly. I cant see any reason why this would not work as expected:

 

SELECT concat(orig_code_1, orig_code_2) = concat(old_code_1, old_code_2)
FROM codes_update_no_join_1a
SET orig_code_1 = new_code_1,
    orig_code_2 = new_code_2

 

ERROR: #1064 - 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 'SET orig_code_1 = new_code_1, orig_code_2 = new_code_2 LIMIT 0, 30' at line 3

 

[OldWest]

Another of many variations I am testing. Of course not working

 

UPDATE codes_update_no_join_1a
WHERE concat(orig_code_1, orig_code_2) = concat(old_code_1, old_code_2),
SET orig_code_1 = new_code_1,
       orig_code_2 = new_code_2

 

[OldWest]

This does not throw any errors! Thats a milestone for me on this BUT it also returns 0 results when there of course should be based on the table data

 

UPDATE codes_update_no_join_1a

SET    orig_code_1 = new_code_1,
       orig_code_2 = new_code_2

WHERE concat(orig_code_1, orig_code_2) = concat(old_code_1, old_code_2)

 

[fenway]

Try the equivalent select for testing.