May 1, 2025 at 2:50 am
Hi everyone
Some fields have a "^" in the name that need to be removed. For example, "^ABC" needs to be "ABC". Not sure what is wrong with my query. Can someone please help?
Query:
DROP TABLE IF EXISTS #TEMP1
DROP TABLE IF EXISTS #PROC_DATES
CREATE TABLE #TEMP1
(
[SYMBOL] [NVARCHAR](10) NOT NULL,
[TXN_DATE] [DATE] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE #PROC_DATES
(
[PROC_DATE] [DATE] NOT NULL
) ON [PRIMARY]
GO
INSERT INTO #TEMP1
VALUES ('ABC','2025-04-24'),
('^DEF','2025-04-25')
INSERT INTO #PROC_DATES
VALUES ('2025-04-25')
SELECT *
FROM #TEMP1
SELECT *
FROM #PROC_DATES
UPDATE #TEMP1
SET T1.SYMBOL = TRIM(REPLACE(T1.SYMBOL,'^',''))
FROM #TEMP1 AS T1 INNER JOIN #PROC_DATES AS T2 ON T1.TXN_DATE = T2.PROC_DATE
WHERE CHARINDEX('^', T1.SYMBOL) = 1
SELECT *
FROM #TEMP1
Error:
Msg 4104, Level 16, State 1, Line 33
The multi-part identifier "T1.SYMBOL" could not be bound.
Completion time: 2025-04-30T19:50:49.8107647-07:00
Thank you
May 1, 2025 at 3:26 am
I was playing around with the query. I figured out the problem. The query didn't like the alias. Once I removed the alias then it worked. That is so odd that SS didn't like the alias.
May 1, 2025 at 7:34 am
When you alias a table in an update, you don't update the table you update the alias
You simply just needed to do
UPDATE T1
Instead of
UPDATE #TEMP1
DROP TABLE IF EXISTS #TEMP1
DROP TABLE IF EXISTS #PROC_DATES
CREATE TABLE #TEMP1
(
[SYMBOL] [NVARCHAR](10) NOT NULL,
[TXN_DATE] [DATE] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE #PROC_DATES
(
[PROC_DATE] [DATE] NOT NULL
) ON [PRIMARY]
GO
INSERT INTO #TEMP1
VALUES ('ABC','2025-04-24'),
('^DEF','2025-04-25')
INSERT INTO #PROC_DATES
VALUES ('2025-04-25')
SELECT *
FROM #TEMP1
SELECT *
FROM #PROC_DATES
UPDATE T1
SET T1.SYMBOL = TRIM(REPLACE(T1.SYMBOL,'^',''))
FROM #TEMP1 AS T1 INNER JOIN #PROC_DATES AS T2 ON T1.TXN_DATE = T2.PROC_DATE
WHERE CHARINDEX('^', T1.SYMBOL) = 1
SELECT *
FROM #TEMP1
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply