0

I have a simple task (they said!), which I need to update the table column using a select statement.

Something like below:

Test table

So let's say this table A, I have a bad data on previous pcsProduces column, right now I want to multiply the cavities and heatcyclecount and then I would like to update the pcsProduces column to a proper value.

The problem is, I have thousands of record, which I really appreciate if someone can help me by showing how to use simple update and select query.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Muhammad Akmal
  • 59
  • 6
  • 1
    if you want to simple update your pcsProduces column by multiply of (cavities * heatcyclecount), then just use update tablename set pcsProduces = cavities * heatcyclecount. – Yogesh Sharma Sep 26 '17 at 05:06
  • Owh God, thank you, that's the easiest way! – Muhammad Akmal Sep 26 '17 at 05:15
  • I think it will be helpful to you. https://stackoverflow.com/questions/2334712/how-do-i-update-from-a-select-in-sql-server – Rajan Sep 26 '17 at 05:18

2 Answers2

2

Just fire SQL Update Command like :

 update tablename set pcsProduces = cavities * heatcyclecount
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
1

You have at least 2 options:

  1. Updating the whole table with an update statement (where clause is optional): 

    update TbYourTable
set pcsProduces = cavities * heatsOrCyclecount
where pcsProduces != cavities * heatsOrCyclecount

  2. Using a computed column (MS SQL Syntax)

    create table [TbYourTable]
(
   [Id]                int       identity(1,1) not null
,  [domainS]           int                     not null
,  [tStations]         int                     not null
,  [itemNo]            int                     not null
,  [defaultCavities]   int                     not null
,  [missingCavities]   int                     not null
,  [cavities]          int                     not null
,  [heatsOrCyclecount] int                     not null
,  [shift]             nvarchar  (max)             null
,  [pcsProduces]       as ([cavities] * [heatsOrCyclecount]) persisted not null -- peristed clause is optional

,  constraint [PK_TbYourTable] primary key nonclustered
   (
      [Id] asc
   )

) on [primary];
Ali
  • 55
  • 1
  • 4