update table form another table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

update table form another table

Author

Topic

papershop
Starting Member

27 Posts

Posted - 2011-07-25 : 23:42:50

dear reader

i have 2 table

1. table machine...like this

machineid Taskid Duedate
106.01 01.FA.05 2011-12-30
106.01 01.FA.05 2012-03-24
106.01 01.FA.05 2012-06-16
106.01 01.FA.05 2012-09-08
106.01 01.FA.05 2011-09-30

2. table workreport... like this

machineid taskid year M1 M2 M3 M4 M5 M6 M7 M8 M9 M10 M11 M12
106.01 01.FA.05 2011 . . . . . . . . . . . .

i need help how to insert 'x' to table workreport where M is month
from the data from table machine the field is duedate,

example :
if the table machine have date 2011-09-30 so field workreport
would be like this

machineid taskid year M1 M2 M3 M4 M5 M6 M7 M8 M9 M10 M11 M12
106.01 01.FA.05 2011 . . . . . . . . X . . .

@papershop

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-07-26 : 02:21:21

formatted...


i have 2 table 

1. table machine...like this

machineid   Taskid      Duedate
106.01	    01.FA.05	2011-12-30 
106.01	    01.FA.05	2012-03-24 
106.01	    01.FA.05	2012-06-16 
106.01	    01.FA.05	2012-09-08 
106.01	    01.FA.05	2011-09-30 

2. table workreport... like this

machineid taskid   year M1 M2 M3 M4 M5 M6 M7 M8 M9 M10 M11 M12
106.01    01.FA.05 2011 .  .  .  .  .  .  .  .  .  .    .  .    

i need help how to insert 'x' to table workreport where M is month
from the data from table machine the field is duedate, 

example :
if the table machine have date 2011-09-30 so field workreport 
would be like this

machineid taskid   year M1 M2 M3 M4 M5 M6 M7 M8 M9 M10 M11 M12
106.01    01.FA.05 2011 .  .  .  .  .  .  .  .  X  .    .  .

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-07-26 : 02:31:26

Try this


update wr
set M1 = case when month(Duedate)=1 then 'X' else M1 end,
    M2 = case when month(Duedate)=2 then 'X' else M2 end,
    M3 = case when month(Duedate)=3 then 'X' else M3 end,
    M4 = case when month(Duedate)=4 then 'X' else M4 end,
    M5 = case when month(Duedate)=5 then 'X' else M5 end,
    M6 = case when month(Duedate)=6 then 'X' else M6 end,
    M7 = case when month(Duedate)=7 then 'X' else M7 end,
    M8 = case when month(Duedate)=8 then 'X' else M8 end,
    M9 = case when month(Duedate)=9 then 'X' else M9 end,
    M10 = case when month(Duedate)=10 then 'X' else M10 end,
    M11 = case when month(Duedate)=11 then 'X' else M11 end,
    M12 = case when month(Duedate)=12 then 'X' else M12 end
from workreport wr 
join machine m on m.machineid = wr.machineid
              and m.Taskid = wr.taskid
              and year(m.Duedate) = wr.year

No, you're never too old to Yak'n'Roll if you're too young to die.

papershop
Starting Member

27 Posts

Posted - 2011-07-26 : 03:18:55

dear webfred...thanks for the script its work..but i want to ask you more because with the script that you give it's only inputed one field..
in the machine table i have 5 data so actualy i want to input 'x' from that data to workreport

so if the month(duedate) at machine are 9,12 in 2011 and 3, 6, 9 in 2012 so iwant to fill the workreport would be like

machineid taskid year M1 M2 M3 M4 M5 M6 M7 M8 M9 M10 M11 M12
106.01 01.FA.05 2011 . . . . . . . . . x . x
106.01 01.FA.05 2012 . . x . . x . . x . . .

could you help me with that?
thx before

@papershop

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-07-26 : 03:28:43

My solution should already do that because there is no hard coded year.

No, you're never too old to Yak'n'Roll if you're too young to die.

papershop
Starting Member

27 Posts

Posted - 2011-07-26 : 05:42:41

dear webfred the solution from you is working but i try if i have 5 data at table machine and then i start the script the table workreport is only inserted 1 data...

thx before

@papershop

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-07-26 : 06:14:56

My solution isn't doing any inserts.
It is only updating existing rows in table workreport.
Do you want it to update existing rows AND inserting not existing rows or what is your need?

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources