update table 1 to table 2 depending date time + ID

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)

update table 1 to table 2 depending date time + ID

Author

Topic

midan1
Starting Member

39 Posts

Posted - 2008-04-26 : 17:04:36

need help please on update only if .

i need to update only the field "val_holiday " (in table B from table A)

and olso to check on table B the "ID" + "new_date" only if exist

and update the field "val_holiday " (in table B)

and at the end of update change the field "field_check_update_if _ok" from 0 to 1
only the row that update in table B


select from table A

update table B 

WHERE ..........................HOW ?

----------------------------------------------------------------- table A

ID fname new_date val_holiday field_check_update_if _ok

---------------------------------------------------------------------------------------------------

111 aaaa 15/03/2008 999 0

111 aaaa 16/03/2008 888 0

111 aaaa 18/03/2008 77 0

111 aaaa 19/03/2008 9 0

111 aaaa 20/03/2008 111 0

111 aaaa 21/03/2008 12 0

222 bbb 02/05/2008 15 0

222 bbb 03/05/2008 16 0

222 bbb 04/05/2008 9 0

222 bbb 05/05/2008 3 0

222 bbb 06/05/2008 90 0

222 bbb 07/05/2008 3 0

222 bbb 08/05/2008 3 0

222 bbb 09/05/2008 3 0

333 ccc 03/04/2008 4 0

333 ccc 04/04/2008 4 0

----------------------------------------------------------------- table B

ID fname new_date val_holiday

----------------------------------------------------

111 aaaa 15/03/2008 1

111 aaaa 16/03/2008 1

111 aaaa 18/03/2008 1

111 aaaa 19/03/2008 1

111 aaaa 20/03/2008 1

111 aaaa 21/03/2008 1

222 bbb 02/05/2008 3

222 bbb 03/05/2008 3

222 bbb 04/05/2008 3

222 bbb 05/05/2008 3

222 bbb 06/05/2008 3

222 bbb 07/05/2008 3

222 bbb 08/05/2008 3

222 bbb 09/05/2008 3

333 ccc 03/04/2008 4

333 ccc 04/04/2008 4

------------------------------------------------------------------------------

TNX for the help and for all

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-27 : 01:55:56

The first update is a strainght forward one. The second update part can be done by creating an update trigger on tableB or by using OUTPUT clause to retrieve the details as shown:-

using OUTPUT variables:-

DECLARE @UpdateRows table (
    ID int NOT NULL, 
    new_date datetime NOT NULL);

UPDATE b
SET b.val_holiday=a.val_holiday
OUTPUT INSERTED.ID,
       INSERTED.new_date
INTO   @UpdateRows
FROM tableB b
INNER JOIN tableA a
ON a.ID=b.ID
AND a.new_date=b.new_date

UPDATE a
SET a.field_check_update_if _ok=1
FROM tableA a
INNER JOIN @UpdateRows u
ON u.ID=a.ID
AND u.new_date=a.new_date

using trigger:-

UPDATE b
SET b.val_holiday=a.val_holiday
FROM tableB b
INNER JOIN tableA a
ON a.ID=b.ID
AND a.new_date=b.new_date


CREATE TRIGGER Trig_tableB
ON tableB 
AFTER UPDATE
AS
BEGIN

UPDATE a
SET a.field_check_update_if _ok=1
FROM tableA a
INNER JOIN INSERTED i
ON i.ID=a.ID
AND i.new_date=a.new_date

END

Please note that the OUTPUT variable method works only in SQL 2005

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources