CODEWITHSUNDEEP
sqlsql-serverms-access
Unnamed
Unnamed

Reputation: 121

Copy fields from one column to another with criteria

I have a table called 02_Month with the following information:

|------------------------------------------------------------------
|     Soc   |    Society     |     USD_STD     |     USD_STD_R     |
|------------------------------------------------------------------|
| AB02      |    Plants       |     3.8        |      1            |
| JK01      |    Trees        |     2.4        |      1            |
| WB09      |    Bushes       |     1.2        |      3            |
| COIN      |    Flowers      |     4.6        |      2            |
| KK99      |    Stones       |     66.9       |      3            |
| TCTC      |    Ruby         |     19.0       |      5            |
| WNOL      |    Steel        |     71.1       |      7            |
|------------------------------------------------------------------

I want to copy what says in column USD_STD to USD_STD_R if the column Soc is COIN or TCTC. I want to have this at the end:

|------------------------------------------------------------------
|     Soc   |    Society     |     USD_STD     |     USD_STD_R     |
|------------------------------------------------------------------|
| AB02      |    Plants       |     3.8        |      1            |
| JK01      |    Trees        |     2.4        |      1            |
| WB09      |    Bushes       |     1.2        |      3            |
| COIN      |    Flowers      |     4.6        |      4.6          |
| KK99      |    Stones       |     66.9       |      3            |
| TCTC      |    Ruby         |     19.0       |      19.0         |
| WNOL      |    Steel        |     71.1       |      7            |
|------------------------------------------------------------------

I wrote this:

UPDATE 02_Month
SET [02_Month].USD_STD_R = USD_STD
WHERE (([02_Month].Soc="COIN") or ([02_Month].Soc="TCTC"));

It is not working as expected. I suppose it's due to the WHERE condition. if I don't write that line it copies the entire column USD_STD in USD_STD_R.

The code I wrote prints 0 in all of the fields of USD_STD_R. Why doesn't it work and how can it be corrected?

Upvotes: 1

Views: 91

Answers (5)

Lee Mac
Lee Mac

Reputation: 16015

The following should be sufficient:

update [02_month] t
set t.usd_std_r = t.usd_std
where t.soc="COIN" or t.soc="TCTC"

A few notes:

  • Since your table name starts with a number, it must be enclosed in square brackets.
  • The use of t is merely an alias to avoid the need to repeatedly write [02_month]
  • You can use either t.soc="COIN" or t.soc="TCTC" or t.soc in ("COIN","TCTC") both should yield the same result.
  • Double-quotes " are typically used by MS Access, but single-quotes ' should also be valid.

Upvotes: 2

Suraj Kumar
Suraj Kumar

Reputation: 5653

You can use inner join update as shown below.

UPDATE a
SET a.USD_STD_R = b.USD_STD --You can change another column here as per your need.
FROM [02_Month] a
INNER JOIN [02_Month] b ON a.soc = b.soc
    AND a.society = b.society
    AND a.USD_STD = b.USD_STD
    AND a.USD_STD_R = b.USD_STD_R
    WHERE a.Soc in ('COIN','TCTC');

Live Demo Here

If you do not want alias name for updating table.

UPDATE [02_Month]
SET [02_Month].USD_STD_R = b.USD_STD --You can change another column here as per your need.
FROM [02_Month] 
INNER JOIN [02_Month] b ON [02_Month].soc = b.soc
    AND [02_Month].society = b.society
    AND [02_Month].USD_STD = b.USD_STD
    AND [02_Month].USD_STD_R = b.USD_STD_R
    WHERE [02_Month].Soc in ('COIN','TCTC');

db<>fiddle Demo

Upvotes: 0

vikas_ved
vikas_ved

Reputation: 1

your relation 02_month with column field USD_STD USD_STD_R but in update statement column field name is STD_USD_R and STD_USD.

Upvotes: 0

Vikram Jain
Vikram Jain

Reputation: 5588

Please, try with below query. string value always within ''.

UPDATE 02_Month 
SET USD_STD_R = USD_STD 
WHERE Soc in ('COIN','TCTC');

Upvotes: 0

Atk
Atk

Reputation: 752

USD_STD instead of STD_USD

Also you can use IN operator

    UPDATE 02_Month
    SET [02_Month].USD_STD_R = USD_STD
    FROM [02_Month]
    WHERE ([02_Month].Soc IN ('COIN','TCTC'));

Upvotes: 0

Related Questions