Please Help...New to MySQL

[pgtjhu]

Hi - I've got two (2) Tables: Old and New
There are two Fields: ID and Status
What I want to do is compare the ID fields between New and Old; if a specific ID is in the New table BUT NOT in the Old table, the I want to update the Status field in the New table to a Status of 2.

Is this doable?

[rch1231]

Yes, the trick is to get a select query that returns the ID's in newtable that do not have an ID in oldTable.

select newtable.id left join on oldtable where newtable.id = oldtable.id
having oldtable.id is null

The you do an update using that select:

Update newtable 
set newtable.status=2
where newtable.id in (select newtable.id 
left join on oldtable 
where newtable.id = oldtable.id
having oldtable.id is null)

[pgtjhu]

Thank you for the effort -- still didn't seem to work. What I tried to do was to create a THIRD table of DISTINCT names from Table 1 & Table 2 -- with the hopes of creating a 4th table where I could update the results of the DISTINCT that was created in the THIRD table. But MyPHPAdmin is treating Table 3 as if it's Table 1. Very strange.

[rch1231]

Did you make sure to use left join and not just join. Left join picks all records from the table name on the left and all matching fields from the table on the right. Once it has the list using HAVING instead of WHERE pulls all records from the result table that match the HAVING entry. I have used this probably 50 times to find unmatched records and know it will work.

Did you try creating the queries separately as I suggested or just see if you could write it from scratch. I used to try and write it all at once and it would take hours to debug and get right. Creating one query at a time makes it easy to debug and verify the data. If you could post the code for your tables based on each of my queries and I will see if I can spot the problem if you like?.

[pgtjhu]

Thank you so much for your help. Here's some code I've been trying:
UPDATE New_table
SET State = 2
WHERE DISTINCT SID from `New` NOT IN (Select SID FROM 'Old') ORDER BY SID;
---
Also tried:
Update New_table
Set State=2
Where SID in (select 'New_table' left join on 'Old' where SID=SID);

Thanks for any help

[kasakit]

Try to select the 2 id in each table and compare
if(newID!=oldID)
{
//what you want to do if the new ID is not = to your old id
}
else
//what you want to do if the new ID is = to your old id

im not sure...