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

SQL Server Development (2000)

UPDATE sentence

Author

Topic

jeusdi
Starting Member

27 Posts

Posted - 2008-01-09 : 09:42:25

I need to update a table as @TINTES. The UPDATE operation has to update GRAVAT field to previous value.

DECLARE @TINTES TABLE (
TINTA VARCHAR(32),
GRAVAT INT,
FITXA INT,
DATA_FITXA DATETIME
)

INSERT INTO @TINTES VALUES ('yellow', 1, 1, '2008-01-09')
INSERT INTO @TINTES VALUES ('blue', 2, 1, '2008-01-09')
INSERT INTO @TINTES VALUES ('red', 3, 1, '2008-01-09')
INSERT INTO @TINTES VALUES ('black', 3, 1, '2008-01-09')
INSERT INTO @TINTES VALUES ('yellow', NULL, 1, '2008-01-10') <<-- After UPDATE, NULL = 1
INSERT INTO @TINTES VALUES ('blue', NULL, 1, '2008-01-10')<<-- After UPDATE, NULL = 2
INSERT INTO @TINTES VALUES ('red', NULL, 1, '2008-01-10')<<-- After UPDATE, NULL = 3
INSERT INTO @TINTES VALUES ('orange', NULL, 1, '2008-01-09')

How Can I update these values using an UPDATE sentence?
Thanks for all in advanced.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-09 : 09:48:33

[code]UPDATE t
SET GRAVAT = (SELECT TOP 1 GRAVAT FROM @TINTES x WHERE x.TINTA = t.TINTA AND x.DATA_FITXA < t.DATA_FITXA ORDER BY DATA_FITXA DESC)
FROM @TINTES t
WHERE GRAVAT IS NULL[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources