CODEWITHSUNDEEP
sql
Whitewolf
Whitewolf

Reputation: 186

Update with select issue

I am unable to assign getdate() to TT.EFFECTIVE_EDATE?

UPDATE
[XXXXX]
 SET
TT.EFFECTIVE_EDATE = getdate(),
TT.CURRENT_FLAG = 'N' 
FROM
[XXXXX] AS TT
INNER JOIN [YYYYY] AS TB
    ON TT.A = TB.A
    AND TT.B = TB.B
WHERE
substring(TB.F,13,8) > substring(TT.F,13,8)

On running I am getting the following error:

The multi-part identifier "TT.EFFECTIVE_EDATE" could not be bound.

Upvotes: 1

Views: 32

Answers (2)

sagi
sagi

Reputation: 40491

You should use the name of the alias you gave to this table at the update statement , so try this:

UPDATE TT
 SET
TT.EFFECTIVE_EDATE = getdate(),
TT.CURRENT_FLAG = 'N' 
FROM
[XXXXX] AS TT
INNER JOIN [YYYYY] AS TB
    ON TT.A = TB.A
    AND TT.B = TB.B
WHERE
    substring(TB.F,13,8) > substring(TT.F,13,8)

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72225

Try this:

UPDATE 
   TT
SET
   EFFECTIVE_EDATE = getdate(),
   CURRENT_FLAG = 'N' 
FROM
   [XXXXX] AS TT
INNER JOIN [YYYYY] AS TB
   ON TT.A = TB.A
      AND TT.B = TB.B
WHERE
   substring(TB.F,13,8) > substring(TT.F,13,8)

Upvotes: 1

Related Questions