New to SQL, need help with query

Question

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...

Answer 1

You need to join the data; something like:

UPDATE d1
SET d1.Profile = d2.Product
FROM [Data] d1 -- dest
INNER JOIN [DATA] d2 -- source
    ON d2.[Import ID] = d1.[ID] AND d2.[Field ID] = 65

(note swapped 2 columns...)

Answer 2

A couple thing to keep in mind when learning sql:

1. it isnt a good idea to have spaces in column names. although they might be easier to read, it makes your queries more difficult. most databases dont allow them at all, and those that do have different ways to specify the columns in queries.

   to work around your problem, perhaps you should try to enclose the column name in backticks (`), or in square brackets ([ ]).

   in any case, instead of a space, please consider an underscore.

2. with that in mind you should also remember that not to put column names in quotes. something like

   SELECT 'Product' FROM Data WHERE 'Field ID'=65

   would not work for two reasons:

   a. Selecting quoted text will return that quoted text. so were the where clause to return two rows, you would get the text 'Product' returned twice.

   b. here your where clause is comparing the text 'Field ID' with the number 65, which would always be false.

hope that helps