PeterBaileyUk
asked on
sql server update query
With Guy and stefans help I have a query that returns ConcatWordText I would like to amend it so that StrShort field in the tblwords(w)
is updated with that result.
is updated with that result.
use Dictionary
go
WITH Filtered
AS ( SELECT W.Word, w.WordPosition, w.clientcode, w.ClientCodeWordPosition, w.StrShort
FROM tblwords w
LEFT JOIN tblwordtags wt ON W.clientcodeWordPosition = wt.clientcodeWordPosition
WHERE wt.word IS NULL and w.ClientCodeWordPosition like '95052350%'
)
SELECT o.ClientCodeWordPosition, o.clientcode, o.StrShort,
RTRIM(( SELECT I.Word + ' '
FROM Filtered I
WHERE I.clientcode = O.clientcode
order by i.WordPosition
FOR
XML PATH('')
)) AS ConcatWordText, o.WordPosition
FROM Filtered O
order by o.WordPosition
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
yes it didnt seem right to put it in the same question as the query was returning the rows.
ASKER
its complaining in the last select by the rtrim all names are red, i suspect its bracketing in there
use Dictionary
go
WITH Filtered
AS ( SELECT W.Word, w.WordPosition, w.clientcode, w.ClientCodeWordPosition, w.StrShort
FROM tblwords w
LEFT JOIN tblwordtags wt ON W.clientcodeWordPosition = wt.clientcodeWordPosition
WHERE wt.word IS NULL and w.ClientCodeWordPosition like '95052350%'
)
update TblWords
SET StrShort = RTRIM (SELECT I.Word + ' '
FROM Filtered I
WHERE I.clientcode = O.clientcode
order by i.WordPosition
FOR
XML PATH('')
)) AS ConcatWordText, o.WordPosition
FROM Filtered O
order by o.WordPosition);
ASKER
i amended slightly to clarify the output
ee.JPG
ee.JPG
Did you try the update command?
ASKER
yes it fails on syntax but cannot see why as mentioned ID: 41748617
ASKER
the select from and where and some brackets are underlined:
SET StrShort = RTRIM (SELECT I.Word + ' '
FROM Filtered I
WHERE I.clientcode = O.clientcode
order by i.WordPosition
FOR
XML PATH('')
)) AS ConcatWordText, o.WordPosition
FROM Filtered O
order by o.WordPosition);
SET StrShort = RTRIM (SELECT I.Word + ' '
FROM Filtered I
WHERE I.clientcode = O.clientcode
order by i.WordPosition
FOR
XML PATH('')
)) AS ConcatWordText, o.WordPosition
FROM Filtered O
order by o.WordPosition);
ASKER
like this
ee.JPG
ee.JPG
That's not my code. You added the last 3 lines. They aren't intended to be there.
ASKER
aha ok I misunderstood.
ASKER
here is the correct snapshot of the screen
ee.JPG
ee.JPG
Did you run it?
Don't forget to test in a non-production environment.
Don't forget to test in a non-production environment.
ASKER
it wont run it says:
Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 15
Incorrect syntax near ')'.
Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 15
Incorrect syntax near ')'.
Ok. I didn't have opportunity to test it.
Can you add SELECT keyword before the RTRIM?
Can you add SELECT keyword before the RTRIM?
ASKER
Ive changed to this but its now :
UPDATE tblwords
SET StrShort = SELECT (RTRIM I.Word + ' '
Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near 'I'.
UPDATE tblwords
SET StrShort = SELECT (RTRIM I.Word + ' '
Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near 'I'.
ASKER
and tried this:
UPDATE tblwords
SET StrShort = SELECT (RTRIM (I.Word + ' ')
FROM Filtered I
WHERE I.clientcode = tblwords.clientcode
ORDER BY i.WordPosition
FOR XML PATH(''))
UPDATE tblwords
SET StrShort = SELECT (RTRIM (I.Word + ' ')
FROM Filtered I
WHERE I.clientcode = tblwords.clientcode
ORDER BY i.WordPosition
FOR XML PATH(''))
Don't add parenthesis:
UPDATE tblwords
SET StrShort = SELECT RTRIM I.Word + '
...
UPDATE tblwords
SET StrShort = SELECT RTRIM I.Word + '
...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
its looking better, nothing underlined now, it says;
Msg 1033, Level 15, State 1, Line 14
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.
I suspect its the last error now
Msg 1033, Level 15, State 1, Line 14
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.
I suspect its the last error now
use Dictionary
go
WITH Filtered
AS ( SELECT W.Word, w.WordPosition, w.clientcode, w.ClientCodeWordPosition, w.StrShort
FROM tblwords w
LEFT JOIN tblwordtags wt ON W.clientcodeWordPosition = wt.clientcodeWordPosition
WHERE wt.word IS NULL and w.ClientCodeWordPosition like '95052350%')
UPDATE tblwords
SET StrShort = RTRIM((SELECT I.Word + ' '
FROM Filtered I
WHERE I.clientcode = tblwords.clientcode
ORDER BY i.WordPosition))
FOR XML PATH('')
ASKER
I did this and i believe, but i will check again that its worked ok.
UPDATE tblwords
SET StrShort = RTRIM((SELECT I.Word + ' '
FROM Filtered I
WHERE I.clientcode = tblwords.clientcode
ORDER BY i.WordPosition
FOR XML PATH('')))
ASKER
Thank you to you both.
Oh, missed a pair of parenthesis.
Glad that you sorted it out.
Cheers
Glad that you sorted it out.
Cheers
https://www.experts-exchange.com/questions/28959379/amend-sql-server-insert-into-query.html