I've got a database table of my very own that i'm attempting to create a query for your appears not so difficult, however i seem like I'm playing up somewhere since the answers are not what they must be.

I essentially possess a table that's such as the following:

Table: Data


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 essentially want to accept word "Hat" in row 2 and put it in to the "Profile" column of row 1, changing the null value there. I'm carrying this out for multiple rows.

Within the situation from the multiple rows I wish to go ahead and take "Profile" column making it comparable to the "Product" column. I only want this to occur within the rows in which the "ID" value matches the "Import ID", and in which the "Area ID" is 65 particularly. Within the example over the "ID" 5 matches the "Import ID" 5, so I wish to go ahead and take "Product" value "Hat" in which the "Area ID" is 65, and put that value in to the "Profile" column in which the ID is 5. My table has over 9000 rows and 600 would need to be transformed in by doing this, with assorted ID's requiring various items placed.

The end result I'd like could 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 which makes sense...

My query was this

UPDATE 'Data' SET 'Profile'='Product' WHERE 'ID'='Import ID' AND 'Field ID'=65

I've also attempted a subquery

UPDATE 'Data' SET 'Profile'= (Choose 'Product' FROM Data WHERE 'Field ID'=65) WHERE 'ID'='Import ID'

This didn't work and i'm just wondering if there's some logic I missing. Thanks to anybody who are able to help, I've been up for a little attempting to appreciate this...

You have to join the information something similar to:

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

A few factor to bear in mind while studying sql:

  1. it is not smart to have spaces in column names. although they could be simpler to see, it can make your queries harder. most databases dont permit them whatsoever, and individuals which do have various ways to specify the posts in queries.

    to operate around your condition, possibly gradually alter enclose the column title in backticks (`), or perhaps in square brackets ([ ]).

    regardless, rather than an area, please consider an underscore.

  2. knowing that it's also wise to keep in mind that to not put column names in quotes. something similar to

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

    wouldn't work with two reasons:

    a. Choosing cited text will return that cited text. so were the where clause to come back two rows, you can get the written text 'Product' came back two times.

    b. here your where clause is evaluating the written text 'Field ID' using the number 65, which may continually be false.

hope that can help