September 21, 2017 at 2:08 pm
I inherited this code. I'm trying to format it to SQL standard indention, but it is hard to determine where to indent with multiple WHERE. Please help me format and indent this SQL code to SQL standard. Here's the code:
UPDATE a SET a.source='CP',
a.PREVENTCOPY=CR.[Landing].PREVENTCOPY,
a.PREVENTPRINT=CR.[Landing].PREVENTPRINT,
a.ISAMOVAR=CR.[Landing].ISAMOVAR,
a.ID=CR.[Landing].ID,
a.MREADONLY=CR.[Landing].MREADONLY,
a.FIELDLEN=CR.[Landing].FIELDLEN,
a.EDITTYPE=CR.[Landing].EDITTYPE,
a.FIELDTITLE=CR.[Landing].FIELDTITLE,
a.MEMOTITLE=CR.[Landing].MEMOTITLE,
a.TITLE=CR.[Landing].TITLE,
a.HELP=CR.[Landing].HELP,
a.COMMENTS=CR.[Landing].COMMENTS,
a.RESERVED=CR.[Landing].RESERVED,
a.FIELDFORMAT=CR.[Landing].FIELDFORMAT,
a.FIELDDATA=CR.[Landing].FIELDDATA,
a.FIELDVALUE=CR.[Landing].FIELDVALUE,
a.LASTUSERID=CR.[Landing].LASTUSERID,
a.tag=CR.[Landing].tag
FROM CR.[Landing] inner join
(select PREVENTCOPY, PREVENTPRINT, ISAMOVAR, ID, MREADONLY, FIELDLEN, EDITTYPE, FIELDTITLE, MEMOTITLE, TITLE, HELP, COMMENTS, RESERVED, FIELDFORMAT, FIELDDATA, FIELDVALUE, LASTUSERID, tag, [source], fieldname, pcfile from CR.[Landing].WHERE PCFILE like '%Road%') a
on CR.[Landing].FIELDNAME= a.FIELDNAME and replace (CR.[Landing].pcfile,'CAR','') = replace(left(a.pcfile, len(a.pcfile)-1),'Road','' )
WHERE replace (CR.[Landing].pcfile,'CAR','') in (select distinct replace(left(pcfile, len(pcfile)-1),'Road','' )from CR.[Landing] WHERE PCFILE like 'Road%')
September 21, 2017 at 2:14 pm
My SQL Prompt settings gave me this. Not perfect, but better.UPDATE a
SET
a.source = 'CP'
, a.PREVENTCOPY = CR.Landing.PREVENTCOPY
, a.PREVENTPRINT = CR.Landing.PREVENTPRINT
, a.ISAMOVAR = CR.Landing.ISAMOVAR
, a.ID = CR.Landing.ID
, a.MREADONLY = CR.Landing.MREADONLY
, a.FIELDLEN = CR.Landing.FIELDLEN
, a.EDITTYPE = CR.Landing.EDITTYPE
, a.FIELDTITLE = CR.Landing.FIELDTITLE
, a.MEMOTITLE = CR.Landing.MEMOTITLE
, a.TITLE = CR.Landing.TITLE
, a.HELP = CR.Landing.HELP
, a.COMMENTS = CR.Landing.COMMENTS
, a.RESERVED = CR.Landing.RESERVED
, a.FIELDFORMAT = CR.Landing.FIELDFORMAT
, a.FIELDDATA = CR.Landing.FIELDDATA
, a.FIELDVALUE = CR.Landing.FIELDVALUE
, a.LASTUSERID = CR.Landing.LASTUSERID
, a.tag = CR.Landing.tag
FROM
CR.Landing
INNER JOIN
(
SELECT
PREVENTCOPY
, PREVENTPRINT
, ISAMOVAR
, ID
, MREADONLY
, FIELDLEN
, EDITTYPE
, FIELDTITLE
, MEMOTITLE
, TITLE
, HELP
, COMMENTS
, RESERVED
, FIELDFORMAT
, FIELDDATA
, FIELDVALUE
, LASTUSERID
, tag
, source
, fieldname
, pcfile
FROM CR.Landing
WHERE PCFILE LIKE '%Road%'
) a ON CR.Landing.FIELDNAME = a.FIELDNAME
AND REPLACE(CR.Landing.pcfile, 'CAR', '') = REPLACE(LEFT(a.pcfile, LEN(a.pcfile) - 1), 'Road', '')
WHERE
REPLACE(CR.Landing.pcfile, 'CAR', '')IN (
SELECT DISTINCT
REPLACE(LEFT(pcfile, LEN(pcfile) - 1), 'Road', '')
FROM CR.Landing
WHERE PCFILE LIKE 'Road%'
);
September 21, 2017 at 2:36 pm
This is much better. Thank You!!
September 22, 2017 at 7:20 am
A slightly more compact version:UPDATE a
SET a.source='CP',
a.PREVENTCOPY = L.PREVENTCOPY,
a.PREVENTPRINT = L.PREVENTPRINT,
a.ISAMOVAR = L.ISAMOVAR,
a.ID = L.ID,
a.MREADONLY = L.MREADONLY,
a.FIELDLEN = L.FIELDLEN,
a.EDITTYPE = L.EDITTYPE,
a.FIELDTITLE = L.FIELDTITLE,
a.MEMOTITLE = L.MEMOTITLE,
a.TITLE = L.TITLE,
a.HELP = L.HELP,
a.COMMENTS = L.COMMENTS,
a.RESERVED = L.RESERVED,
a.FIELDFORMAT = L.FIELDFORMAT,
a.FIELDDATA = L.FIELDDATA,
a.FIELDVALUE = L.FIELDVALUE,
a.LASTUSERID = L.LASTUSERID,
a.tag = L.tag
FROM CR.[Landing] AS L
INNER JOIN (
SELECT PREVENTCOPY, , ISAMOVAR, ID, MREADONLY, FIELDLEN, EDITTYPE, FIELDTITLE, MEMOTITLE, TITLE, HELP,
COMMENTS, RESERVED, FIELDFORMAT, FIELDDATA, FIELDVALUE, LASTUSERID, tag, [source], fieldname, pcfile
FROM CR.[Landing]
WHERE PCFILE LIKE '%Road%'
) AS a
ON L.FIELDNAME= a.FIELDNAME
AND REPLACE(L.pcfile, 'CAR', '') = REPLACE(LEFT(a.pcfile, LEN(a.pcfile) - 1), 'Road', '')
WHERE REPLACE(L.pcfile, 'CAR', '') IN
(
SELECT DISINCT REPLACE(LEFT(pcfile, LEN(pcfile) - 1), 'Road', '')
FROM CR.[Landing]
WHERE PCFILE LIKE 'Road%'
);
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 22, 2017 at 8:53 am
Thank you for the compacted version. Very useful.
September 22, 2017 at 2:10 pm
dan_ie - Friday, September 22, 2017 8:53 AMThank you for the compacted version. Very useful.
You're welcome!
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply