Bad syntax for update statement

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 2000 Forums

Transact-SQL (2000)

Bad syntax for update statement

Author

Topic

WindChaser
Posting Yak Master

225 Posts

Posted - 2005-02-24 : 12:29:29

Folks, I just need a bit of assistance with my bad syntax. I would just like to update a table based on information found in another table.

UPDATE Table1 AS T1
SET MyDate =
(SELECT MIN(Date)
FROM Table2
WHERE Account = T1.Account)

But that doesn't work. What's the correct way to write the T-SQL statement? Thanks!

X002548
Not Just a Number

15586 Posts

Posted - 2005-02-24 : 12:55:38

[code]
USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Account int, MyDate datetime)
CREATE TABLE myTable00(Account int, [Date] datetime)
GO

INSERT INTO myTable99(Account, MyDate)
SELECT 1, '12/31/2004' UNION ALL
SELECT 2, '12/31/2003' UNION ALL
SELECT 3, '12/31/2002'

INSERT INTO myTable00(Account, [Date])
SELECT 1, '12/31/2004' UNION ALL
SELECT 2, '12/31/2003' UNION ALL
SELECT 3, '12/31/2002' UNION ALL
SELECT 1, '12/31/2001' UNION ALL
SELECT 2, '12/31/1999' UNION ALL
SELECT 3, '12/31/1998'
GO

SELECT * FROM myTable99
SELECT * FROM myTable00
GO

UPDATE T1
SET MyDate = MIN_Date
FROM myTable99 T1
JOIN (SELECT Account, MIN([Date]) AS MIN_Date
FROM myTable00
GROUP BY Account) T2
ON T1.Account = T2.Account
GO

SELECT * FROM myTable99
SELECT * FROM myTable00
GO

SET NOCOUNT OFF
DROP TABLE myTable99
DROP TABLE myTable00
GO

[/code]

Brett

8-)

WindChaser
Posting Yak Master

225 Posts

Posted - 2005-02-24 : 13:17:25

Works like a charm. Thanks Brett !!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources