Hello all;
I have a database table of my own that I am trying to construct a query for that seems simple enough, but I feel like I am messing up somewhere because the results are not what they should be.
I basically have a table that is like the following:
Table: Data
Columns:
Row ID Profile Import ID Field ID Product
1 5 Null 5 60 Can
2 0 Null 5 65 Hat
3 0 Null 5 70 Box
4 6 Null 6 60 Fish
I basically want to take the word "Hat" in row 2 and place it into the "Profile" column of row 1, replacing the null value there. I am doing this for multiple rows.
In the case of the multiple rows I want to take the "Profile" column and make it equal to the "Product" column. I only want this to happen in the rows where the "ID" value matches the "Import ID", and where the "Field ID" is 65 specifically. In the example above the "ID" 5 matches the "Import ID" 5, so I want to take the "Product" value "Hat" where the "Field ID" is 65, and place that value into the "Profile" column where the ID is 5. My table has over 9000 rows and 600 would have to be changed in this way, with various ID's needing various products inserted.
The result I would like would be:
Row ID Profile Import ID Field ID Product
1 5 Hat 5 60 Can
2 0 Null 5 65 Hat
3 0 Null 5 70 Box
4 6 Null 6 60 Fish
I pray that makes sense...
My query was this
UPDATE 'Data' SET 'Profile'='Product' WHERE 'ID'='Import ID' AND 'Field ID'=65;
I have also tried a subquery
UPDATE 'Data' SET 'Profile'= (SELECT 'Product' FROM Data WHERE 'Field ID'=65) WHERE 'ID'='Import ID';
This did not work and I am just wondering if there is some logic I missing. Thank you to anyone who can help, I have been up for a bit trying to understand this...