c# or sql Inner Join

I take that back its working in microsoft access but not in my c# sql qeuries for my tables
Im getting a error saying incorrect syntax near 'RIGHT'

so how can i get it to work in c# sql  for the table designer  command text

Hi I posted a accdb for you to try it out, I cant seem to get it to work, try your logic on this

UPDATE Table2 RIGHT JOIN Table1 ON Table2.Value = Table1.Value SET Table1.ETag = Table2.Etag
WHERE (((Table1.Color)="red"));
 

Table1

ID	Fruit	Color	ETag	Value
1	apple	red	beautiful	4
2	orange	orange	TAG_TEST	3
3	cat	white	TAG_TEST	2
4	apple	red	Good	1
5	apple	red	pretty	2

for table 1 there was a mistake the id column suppose to be 1    2   3   4  5

for table2 there is no ID coulumn

there is just a value of a string representation and Etag column of a string representation
there is no table 3, the result you have for table 3 is is suppose to be for table 1.

table2 is a defintion table basically.


now basically you search table1 Color field for red and look at the value for the row of red
 then go to the defnition table 2 and find out what the value you mean

example

if you look at  ID  1 which is the first row, there is a red and the value is 4.
so now you go to table2 to find out what 4 mean. In this case 4 means beautiful.
which comes from table 2, so you take the "beautiful" and update or replace  in table 1 under the etag column


you only search where there is a red. do it for the whole table.

so the end result of table 1  should be

table1

Table1

ID	Fruit	Color	Etag	Value
1	apple	red	beautiful	4
2	orange	orange	TAG_TEST	3
3	cat	white	TAG_TEST	2
4	apple	red	Good	1
5	apple	red	pretty	2

row 2 and 3 will not be updated because there is no red.

You say "searching for the color red and the value red"; I am sorry I did not see that. To search for the value red, add a where clause. I don't understand what you mean by "value red". Note also that the SQL I posted creates a third table, if that is not what you need to do then we can adjust as needed.

The input data I used is:
 

Table1

ID	Fruit	Color	ETag	Value
1	apple	red	TAG_TEST	1
3	cat	white	TAG_TEST	2
1	apple	red	TAG_TEST	2
2	orange	orange	TAG_TEST	3
1	apple	red	TAG_TEST	4

Table2

Value	Etag
0	BAD
1	Good
2	pretty
3	ok
4	beautiful

and using the SQL I posted above, the results I get is:
 

Table3

ID	Fruit	Color	Etag	Value
1	apple	red	beautiful	4
2	orange	orange	ok	3
3	cat	white	pretty	2
1	apple	red	Good	1
1	apple	red	pretty	2

 
So did I use the wrong input data? Is the output I show above not correct?

The code is not replacing the Etags from table2  into table 1 do you know why, I follow this example


SELECT Table1.ID, Table1.Fruit, Table1.Color, Table2.Etag, Table1.Value INTO Table3

FROM Table2 RIGHT JOIN Table1 ON Table2.Value = Table1.Value;

its not working

hi friend,

first do one fetch the value for color red from table 1 using following query:

select Value from table1 where color='red'

Now you have to update the table 2 on the basis of values coming from the first query:

so the final sql statement will be look like this:

update table2 set Etag='pretty' where Value in (select Value from table1 where color='red')

Hope it solve your problem.

Please mark as answer if it helps you.

the result table should be like this which is table1

ID     Fruit      Color      ETag             Value

1      apple     red         beautiful           4

2      orange   orange   TAG_TEST        3

3      cat         white      TAG_TEST         2

1      apple     red         Good                1

1      apple       red       pretty               2