Updating problem - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Updating problem

Author

Topic

nhess80
Yak Posting Veteran

83 Posts

Posted - 2009-08-03 : 18:12:45

I'm trying to update a column in a table based on other data in the same table compared to another table. Below is a query that works correctly.

SELECT CASE WHEN b.state = c.state THEN 'Intra' ELSE 'Inter' END AS InterIntra
FROM Consolidated_BillSum AS a LEFT OUTER JOIN
LergData AS b ON CONVERT(varchar(6), b.npanxx) = LEFT(a.OrigNumber, 6) LEFT OUTER JOIN
LergData AS c ON CONVERT(varchar(6), c.npanxx) = LEFT(a.TermNumber, 6)
WHERE (RIGHT(a.GMTDate, 8) BETWEEN 20090701 AND 20090702)
ORDER BY RIGHT(a.GMTDate, 8)

but when i try to use this in an update statement I can't get it to work for some reason. The query below is what I am using

UPDATE Consolidated_BillSum
SET CallType = CASE WHEN b.state = c.state THEN 'Intra' ELSE 'Inter' END
FROM Consolidated_BillSum AS a LEFT OUTER JOIN
LergData AS b ON CONVERT(varchar(6), b.npanxx) = LEFT(a.OrigNumber, 6) LEFT OUTER JOIN
LergData AS c ON CONVERT(varchar(6), c.npanxx) = LEFT(a.TermNumber, 6) CROSS JOIN
Consolidated_BillSum
WHERE (RIGHT(a.GMTDate, 8) BETWEEN 20090701 AND 20090702) AND (a.CallType IS NULL)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-08-03 : 18:18:13

quote:
Originally posted by nhess80

UPDATE ~~Consolidated_BillSum~~ A
SET CallType = CASE WHEN b.state = c.state THEN 'Intra' ELSE 'Inter' END
FROM Consolidated_BillSum AS a LEFT OUTER JOIN
LergData AS b ON CONVERT(varchar(6), b.npanxx) = LEFT(a.OrigNumber, 6) LEFT OUTER JOIN
LergData AS c ON CONVERT(varchar(6), c.npanxx) = LEFT(a.TermNumber, 6) CROSS JOIN
Consolidated_BillSum
WHERE (RIGHT(a.GMTDate, 8) BETWEEN 20090701 AND 20090702) AND (a.CallType IS NULL)

nhess80
Yak Posting Veteran

83 Posts

Posted - 2009-08-03 : 18:25:39

I have tried that before and it use to work but now when I run it I get SQL Execution Error.. Error Message: Incorrect syntax near 'A'.

quote:
Originally posted by Lamprey

quote:
Originally posted by nhess80

UPDATE ~~Consolidated_BillSum~~ A
SET CallType = CASE WHEN b.state = c.state THEN 'Intra' ELSE 'Inter' END
FROM Consolidated_BillSum AS a LEFT OUTER JOIN
LergData AS b ON CONVERT(varchar(6), b.npanxx) = LEFT(a.OrigNumber, 6) LEFT OUTER JOIN
LergData AS c ON CONVERT(varchar(6), c.npanxx) = LEFT(a.TermNumber, 6) CROSS JOIN
Consolidated_BillSum
WHERE (RIGHT(a.GMTDate, 8) BETWEEN 20090701 AND 20090702) AND (a.CallType IS NULL)

nhess80
Yak Posting Veteran

83 Posts

Posted - 2009-08-03 : 18:28:09

Tried running the below and got Invalid Object name 'A'.

UPDATE A
SET a.CallType = CASE WHEN b.state = c.state THEN 'Intra' ELSE 'Inter' END
FROM Consolidated_BillSum AS a LEFT OUTER JOIN
LergData AS b ON CONVERT(varchar(6), b.npanxx) = LEFT(a.OrigNumber, 6) LEFT OUTER JOIN
LergData AS c ON CONVERT(varchar(6), c.npanxx) = LEFT(a.TermNumber, 6) CROSS JOIN
A
WHERE (RIGHT(a.GMTDate, 8) BETWEEN 20090701 AND 20090702) AND (a.CallType IS NULL)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-03 : 21:24:32

quote:

UPDATE Consolidated_BillSum
SET CallType = CASE WHEN b.state = c.state THEN 'Intra' ELSE 'Inter' END
FROM Consolidated_BillSum AS a LEFT OUTER JOIN
LergData AS b ON CONVERT(varchar(6), b.npanxx) = LEFT(a.OrigNumber, 6) LEFT OUTER JOIN
LergData AS c ON CONVERT(varchar(6), c.npanxx) = LEFT(a.TermNumber, 6) CROSS JOIN
Consolidated_BillSum
WHERE (RIGHT(a.GMTDate, 8) BETWEEN 20090701 AND 20090702) AND (a.CallType IS NULL)

why are you cross join Consolidated_BillSum with itself ? Can you explain what is this query doing ?

KH
[spoiler]Time is always against us[/spoiler]

Haseet
Starting Member

6 Posts

Posted - 2009-08-04 : 01:21:17

Try this..

UPDATE a
SET CallType = CASE WHEN b.state = c.state THEN 'Intra' ELSE 'Inter' END
FROM
Consolidated_BillSum a
LEFT OUTER JOIN
LergData b ON CONVERT(varchar(6), b.npanxx) = LEFT(a.OrigNumber, 6)
LEFT OUTER JOIN
LergData c ON CONVERT(varchar(6), c.npanxx) = LEFT(a.TermNumber, 6)
WHERE
(RIGHT(a.GMTDate, 8) BETWEEN 20090701 AND 20090702)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources