concatenate on update join - SQL Server Forums

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

concatenate on update join

Author

Topic

galego
Starting Member

4 Posts

Posted - 2007-07-23 : 08:43:36

Looking to concat. two fields (w/o having to go to external scripting) in the process of an update that's running through a join.

Currently (to get events that cross today and update from the 'event' table to the current/daily table):

UPDATE tblEmployee SET tblEmployee.Status = tblFuture.Status
FROM tblFuture INNER JOIN tblEmployee
ON tblFuture.EmpID = tblEmployee.EmpID
WHERE DateDiff(DAY,GETDATE(),tblFuture.Start_Date) <= 0 AND DateDiff(DAY,GETDATE(),tblFuture.End_Date) >= 0

But I want to be able to set:

tblEmployee.Status = tblFuture.Status + tblFuture.Remarks ...

That doesn't work.

I've searched a bit here and there, but nothing successful thus far. Kind of new to SQL Server (not that I've done this in Oracle or MySQL). Anyhoo ... any ideas?

TIA!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-07-23 : 09:35:21

>>That doesn't work.

What doesn't work? What happens? You don't drop your car off a the mechanic and simply say "it doesn't work" and expect them to fix it, right? Can you provide some more specific details?

- Jeff
http://weblogs.sqlteam.com/JeffS

galego
Starting Member

4 Posts

Posted - 2007-07-23 : 10:02:53

The line preceding 'That' with the elipse

tblEmployee.Status = tblFuture.Status + tblFuture.Remarks ...

... in place of the first line of working code at the top (in conjunction with the whole query, hence the elipse)

UPDATE tblEmployee SET tblEmployee.Status = tblFuture.Status

Specific error is 'invalid data type. operator equals add, type equals ntext'

So ... type mismatch of some sort, it would seem.

tblFuture.Status + CHAR( tblFuture.Remarks)

doesn't seem to do it (and no, I have no idea why they created that field as 'ntext')

Re: mechanic analogy ... the mechanic may 'fix' your car, but it would depend on how much they could read into your vague comments. Also they just may not 'fix' what you wanted fixed or the bill you thought you'd get for it ... happy Monday to you too! :p

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-07-23 : 10:21:56

See, not only is the error message crucial, but the fact that the column in ntext.

You can use CONVERT() to convert a ntext value to an nvarchar. Something like:

set tblFutureStatus = tblFuture.Status + convert(nvarchar(8000), tblFuture.Remarks)

- Jeff
http://weblogs.sqlteam.com/JeffS

galego
Starting Member

4 Posts

Posted - 2007-07-23 : 10:35:00

Yeah, yeah ... I'm normally verbose. Can I get a freebie today?

Thanks ... works like a charm!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-07-23 : 11:57:20

no problem, glad to have helped!

- Jeff
http://weblogs.sqlteam.com/JeffS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-23 : 12:32:02

And this might help you using any present index

~~WHERE DateDiff(DAY,GETDATE(),tblFuture.Start_Date) <= 0 AND DateDiff(DAY,GETDATE(),tblFuture.End_Date) >= 0~~
WHERE DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 0) BETWEEN tblFuture.Start_Date AND tblFuture.End_Date

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources