Update fields with same table values

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

General SQL Server Forums

New to SQL Server Programming

Update fields with same table values

Author

Topic

nitsmooth
Yak Posting Veteran

68 Posts

Posted - 2012-09-11 : 07:36:49

I am not able to get the below code to work. what should i do??
no updates take place?


update table1 set
Signature2 = Signature1,
Signature3 = Signature2,
Date2 = Date1,
Date3 = Date2,
Time2 = Time1,
Time3 = Time2,
Signature1 = Signature3,
Signature4 = Signature4,
Signature5 = Signature5,
Date1 = Date3,
Date4 = Date4
where CallID in(select CallID from table2 where CallType = 'Custom')

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-11 : 07:41:56

Are you getting the following syntax error, or is it something else?

Msg 156, Level 15, State 1, Line 18
Incorrect syntax near the keyword 'WHERE'.

If it is the syntax error, that is because you have an extra comma just before the WHERE clause. Removing that will fix the syntax error. You don't really need the last column (Date4 = Date4).

UPDATE detail
SET    Signature2     = Signature1,
       Signature3     = Signature2,
       Date2          = Date1,
       Date3          = Date2,
       Time2          = Time1,
       Time3          = Time2,
       Signature1     = Signature3,
       Signature4     = Signature4,
       Signature5     = Signature5,
       Date1          = Date3,
       Date4     = Date4
WHERE  CallID IN (SELECT CallID
                  FROM   CallLog
                  WHERE  CallType = 'Custom')

nitsmooth
Yak Posting Veteran

68 Posts

Posted - 2012-09-11 : 07:53:11

no no that was just a copy paste error, actually the problem is that i am not able to update the fields of the table with other fields of the same table....i am able to run the query but no update takes place

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-09-11 : 08:05:45

are you sure no update is taking place? run the subquery first to see if there are any results (select CallID from table2 where CallType = 'Custom'). If there are no CallIDs returned, that is the problem.

Otherwise, you need to double check to make sure the update has not happened.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

nitsmooth
Yak Posting Veteran

68 Posts

Posted - 2012-09-11 : 08:37:49

i can assure you the update is not happening, the callid is there...you can try it on a test table

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-09-11 : 08:44:44

Give us some DDL and sample data. It should work.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

nitsmooth
Yak Posting Veteran

68 Posts

Posted - 2012-09-11 : 09:57:59

seems there some problem with my sql server, i tried the same query on my teammates machine and there it works